如何在Oracle中使用内部联接进行更新 [英] How to update with inner join in Oracle
问题描述
有人可以通过PL SQL中的UPDATE语句验证内部联接是否有效吗? 例如
Could someone please verify whether inner join is valid with UPDATE statment in PL SQL? e.g.
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
推荐答案
此synthax在Oracle SQL中不起作用.
This synthax won't work in Oracle SQL.
在Oracle ,如果表是保留键" ,即:
In Oracle you can update a join if the tables are "key-preserved", ie:
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
假设b_pk
是b
的主键,则此连接是可更新的,因为对于A的每一行,B最多 行,因此更新是确定性的.
Assuming that b_pk
is the primary key of b
, here the join is updateable because for each row of A there is at most one row from B, therefore the update is deterministic.
在您的情况下,由于更新后的值不依赖于另一个表,因此您可以使用具有EXIST条件的简单更新,如下所示:
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:
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)
这篇关于如何在Oracle中使用内部联接进行更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!