在 Oracle 中使用多行选择查询进行更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16817067/
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
Update using select query with multiple Rows in Oracle
提问by Sajini
Can any one please help me to solve this issue
任何人都可以帮我解决这个问题吗
Table Name:RW_LN
表名：RW_LN
LN_ID RE_LN_ID RE_PR_ID
LN001 RN001 RN002
LN002 RN002 RN003
LN003 RN003 RN001
LN004 RN001 RN002
MY Update Query is:
我的更新查询是：
update table RW_LN set RE_LN_ID=(
select LN_ID
from RW_LN as n1,RW_LN as n2
where n1.RE_LN_ID = n2.RE_PR_ID)
MY Expected Result is:
我的预期结果是：
LN_ID RE_LN_ID
LN001 LN003
LN002 LN004
LN003 LN002
LN004 LN003
This above query shows error as SUB QUERY RETURNS MULTIPLE ROWS.Can any one provide the solution for this, I am Beginner in Oracle 9i.So Stuck in the logic
上面的查询显示错误为SUB QUERY RETURNS MULTIPLE ROWS。任何人都可以为此提供解决方案，我是 Oracle 9i 的初学者。所以陷入了逻辑
回答by Woot4Moo
you can try to solve this with a distinct
你可以尝试解决这个问题 distinct
update table RW_LN set RE_LN_ID=(
select distinct LN_ID
from RW_LN as n1,RW_LN as n2
where n1.RE_LN_ID = n2.RE_PR_ID)
if that still returns multiple rows, it means you are missing a join somewhere along the way or potentially have a bad schema that needs to use primary keys.
如果仍然返回多行，则意味着您在此过程中的某处缺少连接，或者可能有需要使用主键的错误架构。
回答by David Aldridge
Just guessing, but possibly this is what you want.
只是猜测，但可能这就是您想要的。
update
RW_LN n1
set
RE_LN_ID=(
select n2.LN_ID
from RW_LN n2
where n1.RE_LN_ID = n2.RE_PR_ID)
where exists (
select null
from RW_LN n2
where n1.RE_LN_ID = n2.RE_PR_ID and
n2.ln_id is not null)
At the moment there is no correlation between the rows you are updating and the value being returned in the subquery.
目前，您正在更新的行与子查询中返回的值之间没有相关性。
The query reads as follows:
查询内容如下：
For every row in RW_LN change the value of RE_LN_ID to be:
the value of LN_ID in a row in RW_LN for which:
the RE_PR_ID equals the original tables value of RE_LN_ID
IF there exists at least one row in RW_LN for which:
RE_PR_ID is the same as RE_LN_ID in the original table AND
LN_ID is not null
回答by Rapha?l Althaus
If you want to take the "biggest" corresponding LN_ID, you could do
如果你想取“最大”对应的 LN_ID，你可以这样做
update RW_LN r1
set r1.RE_LN_ID = (select MAX(LN_ID)
FROM RW_LN r2
where r1.RE_LN_ID = r2.RE_PR_ID);
see SqlFiddle
But you should explain why you choose (as new RE_LN_ID) LN004 instead of LN001 for LN_ID LN002 (cause you could choose both)
但是您应该解释为什么您选择（作为新的 RE_LN_ID）LN004 而不是 LN001 作为 LN_ID LN002（因为您可以同时选择两者）
