/*+ BYPASS_UJVC*/

 在使用implict update table 时，oracle会自动检查唯一性约束，因此关联的2张表的字段一定要有唯一性约束，否则会报错！但是可以使用hints：/*+ BYPASS_UJVC*/ 屏蔽掉对唯一性的检查。具体测试过程如下： 

create table student(
   student_id number,
   name varchar2(30),
   birthday date,
   sex char(1),
   constraints pk_student_id primary key(student_id)
);

insert into student(student_id, name, birthday, sex) values ('2', '高伟刚',
                    to_date('1991-01-09', 'yyyy-mm-dd'), 'M');
insert into student(student_id, name, birthday, sex) values ('3', '高红成',
                    to_date('1991-01-02', 'yyyy-mm-dd'), 'M');
insert into student(student_id, name, birthday, sex) values ('4', '彭传志',
                    to_date('1991-01-24', 'yyyy-mm-dd'), 'M');

create table teacher(
   teacher_id number unique,
   name varchar2(30),
   birthday date,
   sex char(1)，
   constraints pk_teacher_id primary key(teacher_id)
);
insert into teacher(teacher_id, name, birthday, sex) values ('2', '李艳',
                    to_date('1971-01-09', 'yyyy-mm-dd'), 'M');
insert into teacher(teacher_id, name, birthday, sex) values ('3', '熊松涛',
                    to_date('1976-04-02', 'yyyy-mm-dd'), 'M');

 --student_id和teacher_id都是主键，具有唯一性约束

update (select s.name as s_name,
               t.name as t_name from student s, teacher t 
                               where s.student_id =  t.teacher_id)--因为student_id和teacher_id是主键，所以可以直接implict update
         set s_name = t_name

 

--假如student_id和teacher_id其中有一个不是主键或2个都不是主键(没有唯一性约束)时，使用implict udpate 会报如下错误：

   cannot modify a column which maps to a non key-preserved table

解决：我们可以使用hints:/*+BYPASS_UJVC*/屏幕掉唯一性检查，屏幕唯一性检查效率会提高。

update  (select /*+ BYPASS_UJVC */ --屏蔽掉对student_id与teacher_id唯一性检查
                s.name as s_name, 
                t.name as t_name from student s, teacher t
                                where s.student_id =  t.teacher_id)
         set s_name = t_name