Oracle SQL相关更新 [英] Oracle SQL correlated update

问题描述

我有3张桌子:

t1.columns: a,c
t2.columns: a,b
t3.columns: b,c,d

现在我要用t3.d更新t1.c.但是我不能只使用t1.c = t3.c从t3更新t1，我还必须经过t3.b = t2.b和t1.a = t2.a.

Now what I want is to update t1.c with t3.d. But I can't just update t1 from t3 using t1.c = t3.c I also have to go though t3.b = t2.b and t1.a = t2.a.

我尝试过这样的事情:

UPDATE table1 t1
   SET t1.c = (select t3.d
               from table2 t2, table3 t3
               where t2.b = t3.b and t1.a = t2.a)                                  
 WHERE EXISTS ( SELECT 1 FROM table2 t2, table3 t3 WHERE t1.c = t3.c and t1.a = t2.a);

此代码生成错误消息:ORA-01427:单行子查询返回多个行

This code generates error-msg: ORA-01427: single-row subquery returns more than one row

推荐答案

如果t1与t2之间或t2与t3之间存在一对多的关系，则t1中的每一行都会有很多匹配项.如果您知道t3中属于t1中同一行的所有行在d中具有相同的值，则可以使用DISTINCT删除(相同)重复项.

If there is a one-to-many relationship between t1 and t2 or between t2 and t3 you will get many matches for each row in t1. If you know that all rows in t3 that belong to the same row in t1 have the same value in d, then you can use DISTINCT to remove (identical) duplicates.

UPDATE table1 t1
   SET t1.c = (select DISTINCT t3.d
               from table2 t2, table3 t3
               where t2.b = t3.b and t1.a = t2.a)                                  
 WHERE EXISTS ( SELECT 1 FROM table2 t2, table3 t3 WHERE t1.c = t3.c and t1.a = t2.a);

这篇关于Oracle SQL相关更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！