Oracle SQL 相关更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15640851/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL correlated update
提问by President Camacho
I got three tables:
我得到了三张桌子：
t1.columns: a,c
t2.columns: a,b
t3.columns: b,c,d
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.
现在我想要的是用 t3.d 更新 t1.c。但我不能只使用 t1.c = t3.c 从 t3 更新 t1 我还必须通过 t3.b = t2.b 和 t1.a = t2.a。
I've tried something like this:
我试过这样的事情：
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);
This code generates error-msg: ORA-01427: single-row subquery returns more than one row
此代码生成错误消息：ORA-01427：单行子查询返回多于一行
回答by Klas Lindb?ck
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 DISTINCTto remove (identical) duplicates.
如果 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);
回答by Gordon Linoff
You have a subquery that is returning more than one row. Use rownumto get just one row:
您有一个返回多行的子查询。用于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);
回答by President Camacho
Sorry for the confusion but I solved it:
抱歉造成混乱，但我解决了：
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)
回答by Lal
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);
