Getting error when doing an update query using joins-CodePudding

UPDATE A
SET foo = 'bar',
car = 'bmw'
FROM TableA A
JOIN TableB B
    ON A.col1 = B.colx
    AND A.STATUS = B.STATUS
WHERE A.nbr = '1234'
      and A.STATUS IN (K,Y)
      and A.FILE_TYPE = 'R'

I am trying to use the update function above but I keep getting the error it is not properly ended when I try to run it. Can someone tell me where the syntax error is?

CodePudding user response：

In the Oracle database, when updating a table from another table, it's often easier to use the MERGE statement.

MERGE INTO TableA A
USING TableB B ON (
      A.col1 = B.colx
  AND A.STATUS = B.STATUS
  AND A.nbr = 1234
  AND A.STATUS IN (K,Y)
  AND A.FILE_TYPE = 'R'
)
WHEN MATCHED THEN UPDATE 
 SET foo = 'bar'
   , car = 'bmw';

CodePudding user response：

In Oracle you can not update a join query. You may only update a table (and im some cases a view or a subquery).

Fortunately you can simple rewrite your update without the join by passing the condition in the where clause.

update  TableA a
set foo = 'bar',
car = 'bmw'
WHERE a.nbr = '1234'
and a.STATUS IN ('K','Y')
and a.FILE_TYPE = 'R'
and (col1, status) in (select colx,status from tableB);

This is the simplest solution for you problem as described.

Note if you realy needs a join, e.g. if you need to update the columns of the tableA with the columns of the tableB such as

UPDATE A
SET a.foo = b.foo,
a.car = b.car
....

You may use in Oracle an updatable join view as follows

update (
select a.*, b.foo new_foo, b.car new_car
FROM TableA A
JOIN TableB B
    ON A.col1 = B.colx
    AND A.STATUS = B.STATUS
WHERE A.nbr = '1234'
      and A.STATUS IN ('K','Y')
      and A.FILE_TYPE = 'R'
)
set foo = new_foo,
car = new_car

Note that the join query is enclosed in parenthesis to get the rigth syntax.

Note also that the tableB must have a primary key (or unique index) on the columns colx,status to be able to update. Otherwise you get exception ORA-01779: cannot modify a column which maps to a non key-preserved table