甲骨文的SQL相关更新(Oracle SQL correlated update)

我有三个表：

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.

我已经试过这样的事情：

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：单行子查询返回多个行

Answer 1:

如果有T1和T2或T2和T3之间的一个一对多的关系，你会得到很多比赛对于t1的每一行。 如果你知道，在T3属于同一行中T1的所有行有d相同的值，那么你可以使用DISTINCT删除（相同的）重复。

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

Answer 2:

您有返回多行的子查询。 使用rownum得到的只是一个行：

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

Answer 3:

很抱歉的混乱，但我解决它：

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

Answer 4:

UPDATE table1 t1
   SET t1.c = (select MAX(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 correlated update