在使用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