SQL 如何在 Oracle 中使用内部联接进行更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9410870/
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
How to update with inner join in Oracle
提问by Nil Pun
Could someone please verify whether inner join is valid with UPDATE statment in PL SQL? e.g.
有人可以验证内部联接是否对 PL SQL 中的 UPDATE 语句有效吗？例如
Update table t
set t.value='value'
from tableb b inner join
on t.id=b.id
inner join tablec c on
c.id=b.id
inner join tabled d on
d.id=c.id
where d.key=1
回答by Vincent Malgrat
This synthax won't work in Oracle SQL.
这个 synthax 在 Oracle SQL 中不起作用。
In Oracle you can update a join if the tables are "key-preserved", ie:
在 Oracle 中，如果表是 "key-preserved"，则可以更新连接，即：
UPDATE (SELECT a.val_a, b.val_b
FROM table a
JOIN table b ON a.b_pk = b.b_pk)
SET val_a = val_b
Assuming that b_pkis the primary key of b, here the join is updateable because for each row of A there is at mostone row from B, therefore the update is deterministic.
假设这b_pk是 的主键b，这里的连接是可更新的，因为对于 A 的每一行，最多有一行来自 B，因此更新是确定性的。
In your case since the updated value doesn't depend upon another table you could use a simple update with an EXIST condition, something like this:
在您的情况下，由于更新的值不依赖于另一个表，您可以使用带有 EXIST 条件的简单更新，如下所示：
UPDATE mytable t
SET t.VALUE = 'value'
WHERE EXISTS
(SELECT NULL
FROM tableb b
INNER JOIN tablec c ON c.id = b.id
INNER JOIN tabled d ON d.id = c.id
WHERE t.id = b.id
AND d.key = 1)
回答by L Petre
update t T
set T.value = 'value'
where T.id in (select id from t T2, b B, c C, d D
where T2.id=B.id and B.id=C.id and C.id=D.id and D.key=1)
-- t is the table name, T is the variable used to reffer to this table
