Using a subquery instead of a table name in an Oracle Update Statement

Question

I need write an update statement that used multiple tables to determine which rows to update, since in Oracle, multiple tables aren't allowed. The following query will return a "ORA-00971: Missing SET keyword" error

UPDATE
  TABLE1 a,
  TABLE2 b
SET
  a.COL1 = 'VALUE'
WHERE
  a.FK = b.PK
  AND b.COL2 IN ('SET OF VALUES')

Looking up the UPDATE statement syntax on oracle, I found the following link, which shows that you can use a subquery in place of a table name.

When I tried to write the query like this, I got a "ORA-01779: Cannot modify a column which maps to a non key-preserved table"

UPDATE
  (
    SELECT
      a.COL1
    FROM
      TABLE1 a,
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
  ) update_tbl
SET
  update_tbl.COL1 = 'VALUE'

I did rewrite the query (show below) using an EXISTS statement instead and it works fine, but would still like to know how this is done.

UPDATE
  TABLE1 update_tbl
SET
  update_tbl.COL1 = 'VALUE'
WHERE
  EXISTS (
    SELECT
      1
    FROM
      TABLE1 a
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
      AND update_tbl.PK = a.PK
  )

Thanks! -Nate

Answer 1

Another option:

UPDATE TABLE1 a
SET a.COL1 = 'VALUE'
WHERE a.FK IN
( SELECT b.PK FROM TABLE2 b
  WHERE b.COL2 IN ('SET OF VALUES')
)

Your second example would work if (a) the view included the declared PK of TABLE1:

UPDATE
  (
    SELECT
      a.COL1, a.PKCOL
    FROM
      TABLE1 a,
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
  ) update_tbl
SET
  update_tbl.COL1 = 'VALUE'

... and (b) TABLE1.FK was a declared foreign key to TABLE2

(By declared I mean that a constraint exists and is enabled).

Answer 2

I find that a nice, quick, consistent way to turn a SELECT statement into an UPDATE is to make the update based on the ROWID.

UPDATE
  TABLE1
SET
  COL1 = 'VALUE'
WHERE
  ROWID in
    (
    SELECT
      a.rowid
    FROM
      TABLE1 a,
      TABLE2 b
    WHERE
      a.FK = b.PK
      AND b.COL2 IN ('SET OF VALUES')
    )

So, your inner query is defining the rows to update.