How to update with inner join in Oracle

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'
 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)

Tags:

Sql

Oracle