利用其他表的数据update当前表

 

利用其他表的数据update当前表

   

2007-08-22 14:56:00

大 中 小

标签：学习公社

利用其他表的数据update当前表

转贴：http://www.blogjava.net/alex/archive/2007/08/19/137929.html

经常用到update语句，不过很少用到其他表的数据来更新当前表，这次用到了，总结一下

UPDATE
( SELECT /*+ BYPASS_UJVC */
a.groupname a1,b.groupname b1 from t_account_temp a,t_certificode_temp b
where a.certificode = b.certificode
)
SET a1=b1;

特别的地方是要加  /*+ BYPASS_UJVC */,让oracle不检查健的约束，否则会报ORA-01779错误,不过前提是你确认确实不会有多个b表的值出现，或者即使有多个值也没有影响。

或者是：

update (
 select /*+use_hash(a,b)*/

a.a a1 ,b.b b1
 from a a,b b
where a.c = b.c
 ) set b1 = a1

我试过了，需要在a.bill_id， b.bill_id列上有唯一约束或索引才可以，否则报错：
ORA-01779: cannot modify a column which maps to a non key-preserved table

 

Oracle中试图对一个子查询进行更新时可能会出现ORA-01779错误。该错误的内容为：

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

例如，使用以下的更新查询就会出现该错误。

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);
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; ORA-01779: cannot modify a column which maps to a non-key-preserved table

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

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

CREATE UNIQUE INDEX test2_idx_001 ON test2 (id);

之后上面的更新就可以执行了。

另外也可以强制 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就会被更新多次而导致意想不到的结果。