Oracle 多表关联更新

时间：2021-10-15 07:14:26

drop table course;

create table course (

id integer,

teacherNo integer,

teacherDesc varchar2(100),

teacherName varchar2(50),

courseName varchar2(50)

);

insert into course values(1,100,'Mr.zhang','ZhangSan','English');

insert into course values(2,101,'Mr.wang','WangWu','History');

insert into course values(2,101,'Mr.wang','WangWu','Chinese');

update course set teacherDesc='Good Teacher' where teacherNo=101;

commit;

drop table teacher;

create table teacher(

id integer,

teacherDesc varchar2(100),

teacherName varchar2(50)

);

insert into teacher values(100,'Mr.zhang','ZhangSan');

insert into teacher values(101,'Mr.wang','WangWu');

update teacher set teacherDesc='Excellent Teacher' where id=101;

commit;

select c.teacherdesc, c.teachername, t.teacherdesc, t.teachername

  from course c, teacher t

 where c.teacherno = t.id

   and (c.teacherdesc != t.teacherdesc or c.teachername != t.teachername);

update course c

   set (c.teacherdesc, c.teachername) =

       (select t.teacherdesc, t.teachername

          from teacher t

         where c.teacherno = t.id

           and (c.teacherdesc != t.teacherdesc or

               c.teachername != t.teachername))

 where exists (select 1

          from teacher t

         where c.teacherno = t.id

           and (c.teacherdesc != t.teacherdesc or

               c.teachername != t.teachername));
commit;

select c.teacherdesc, c.teachername, t.teacherdesc, t.teachername

  from course c, teacher t

 where c.teacherno = t.id

   and (c.teacherdesc != t.teacherdesc or c.teachername != t.teachername);

select c.teacherdesc, c.teachername, t.teacherdesc, t.teachername

  from course c, teacher t

 where c.teacherno = t.id;

 　　

Oracle 多表关联更新
update时报ORA-01779：
数据准备：

CREATE TABLE test1 ( id integer primary key, num integer );

INSERT INTO test1 VALUES (1,0);

INSERT INTO test1 VALUES (2,0);

INSERT INTO test1 VALUES (3,0);

INSERT INTO test1 VALUES (4,0);  

CREATE TABLE test2 ( id integer, num integer, upd integer );

INSERT INTO test2 VALUES (1,10, 0);

INSERT INTO test2 VALUES (2,20, 1); 

commit;

执行如下更新语句会报错：ORA-01779: 无法修改与非键值保存表对应的列
01779, 00000, "cannot modify a column which maps to a non key-preserved table"
// *Cause: An attempt was made to insert or update columns of a join view which
//        map to a non-key-preserved table.
// *Action: Modify the underlying base tables directly.

UPDATE (SELECT T1.ID ID1, T1.NUM NUM1, T2.ID ID2, T2.NUM NUM2

          FROM TEST1 T1, TEST2 T2

         WHERE T1.ID = T2.ID

           AND T2.UPD = 1)

   SET NUM1 = NUM2;

这个错误的意思是，子查询的结果中，更新数据源(test2)的内容不唯一，导致被更新对象(test1)中的一行可能对应数据源(test2)中的多行。
本例中，test2表的id不唯一，因此test2表中可能存在id相同但是num不相同的数据，这种数据是无法用来更新 test1 的。【这个报错属于事前检查，没有通过校验】

解决方法就是保证数据源的唯一性，例如本例中可以为test2.id创建一个唯一索引：

CREATE UNIQUE INDEX test2_idx_001 ON test2 (id);

之后上面的更新就可以执行了。
也可以使用如下命令来使用test2中记录具有唯一性

alter table test2 modify id unique;

另外也可以强制 Oracle 执行，方法是加上 BYPASS_UJVC 注释。

UPDATE (SELECT /*+ BYPASS_UJVC */

         T1.ID ID1, T1.NUM NUM1, T2.ID ID2, T2.NUM NUM2

          FROM TEST1 T1, TEST2 T2

         WHERE T1.ID = T2.ID

           AND T2.UPD = 1)

   SET NUM1 = NUM2;

BYPASS_UJVC的作用是跳过Oracle的键检查。
这样虽然能够执行了，但是如果test2中存在不唯一的数据，test1就会被更新多次而导致意想不到的结果。【有风险，不建议使用】　　

http://www.linuxidc.com/Linux/2012-08/69089.htm

相关文章