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

Each row in the result set of the query in your UPDATE clause must map back to one and only one row in the table you are trying to update, and in a way that Oracle can follow automatically. Since the query is really a view, one way to think about it is that Oracle needs to be able to join the view back to the target table, in order to know what row to update.

This essentially means that you need to include the primary key of the destination table in that query. You might be able to use some other unique index field(s) too, but I can't guarantee the Oracle DBMS is smart enough to allow that.

Answer 2

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 3

When you perform an update you can obviously only tell the system to update the value to a single new value -- telling it to update "X" to both "Y" and "Z" doesn't make sense. So, when you base an update on the result of an inline view Oracle performs a check that there are sufficient constraints in place to prevent a modified column being potentially updated twice.

In your case I expect that TABLE2.PK is not actually a declared primary key. If you place a primary or unique constraint on that columnthen you'd be good to go.

There is an undocumented hint to byass the update join cardinality check, used internally by Oracle, but I wouldn't advise using it.

One workaround for this is to use a MERGE statement, which is not subject to the same test.

Answer 4

The syntax of your example is fine, but Oracle requires that the subquery include primary keys. That's a pretty significant limitation.

On a related note, you can also use parentheses to use 2 or more fields in an IN statement, as in:

UPDATE
  TABLE1 update_tbl
SET
  update_tbl.COL1 = 'VALUE'
WHERE
  (update_tbl.PK1, update_tbl.pk2) in(
                      select some_field1, some_field2
                      from some_table st
                      where st.some_fields = 'some conditions'
                      );

Answer 5

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.

Answer 6

Hi, I'm looking for an UPDATE statement where it will update a single duplicate row only and remain the rest (duplicate rows) intact as it is, using ROWID or something else or other elements to utilize in Oracle SQL or PL/SQL? Thanks a lot, Val.