Oracle SQL 相关更新

2022-01-17 00:00:00 sql-update sql subquery oracle

我有三张桌子:

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);

相关文章