Oracle Update with multiple inner Joins

Question

I have 3 tables.

Table 2 is the table to update. Table 3 contains the value I want to update table 2 with. The ID in table 2 must exist in table 1.

I have tried

UPDATE (SELECT table2.ID, table2.CODE, table2.STATUS, table3.STATUS as upCode
FROM table1
INNER JOIN table3 ON table3.id = table1.id
INNER JOIN table2 ON table2.id = table3.id
WHERE table2.CODE = 'X' and table2.status = 'Y') u
SET u.CODE = 'Z', u.STATUS = upCode;

However, I am getting the error

ORA-01779: cannot modify a column which maps to a non key-preserved table

I get the feeling I need to use a MERGE query but I'm struggling to figure out how to account for table 1

Answer 1

You can use a MERGE statement:

MERGE INTO table2 dst
USING (
  SELECT ID,
         STATUS
  FROM   table3 t3
  WHERE  EXISTS(
           SELECT 1
           FROM   table1 t1
           WHERE  t3.id = t1.id
         )
) src
ON (dst.id = src.id)
WHEN MATCHED THEN
  UPDATE
  SET   dst.code   = 'Z',
        dst.status = src.status
  WHERE dst.code   = 'X'
  AND   dst.status = 'Y';

Which, for the sample data:

CREATE TABLE table2 (id, code, status) AS
  SELECT 1, 'X', 'Y' FROM DUAL UNION ALL
  SELECT 2, 'X', 'Y' FROM DUAL UNION ALL
  SELECT 3, 'X', 'Y' FROM DUAL;

CREATE TABLE table3 (id, status) AS
  SELECT 1, 'A' FROM DUAL UNION ALL
  SELECT 2, 'B' FROM DUAL UNION ALL
  SELECT 4, 'D' FROM DUAL;

CREATE TABLE table1 (id) AS
  SELECT 1 FROM DUAL UNION ALL
  SELECT 1 FROM DUAL UNION ALL
  SELECT 2 FROM DUAL UNION ALL
  SELECT 3 FROM DUAL;

Then, after the MERGE, table2 contains:

ID	CODE	STATUS
1	Z	A
2	Z	B
3	X	Y

fiddle