Skip to content

How to update with inner join in Oracle

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
inner join tablec c on
inner join tabled d on
where d.key=1


This synthax won’t work in Oracle SQL.

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

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.

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'
          FROM tableb b
         INNER JOIN tablec c ON =
         INNER JOIN tabled d ON =
         WHERE =
           AND d.key = 1)