CODEWITHSUNDEEP
oracle-databasems-accesssql-update
addcolor
addcolor

Reputation: 503

Update with inner join in Oracle not working

Converting query from Access to Oracle is not working

Access

UPDATE 
     Table1 
INNER JOIN      
     Table2
ON  (Table1.ID = Table2.ID) AND (Table1.AREA = Table2.AREA) 
SET Table2.LAT = Table1.LAT, Table2.LON = Table1.LON;

Oracle

UPDATE 
    Table1 
SET
    Table2.LAT= (SELECT Table1.LAT FROM Table1  WHERE Table1.ID = Table2.ID  AND Table1.AREA = Table2.AREA );

Getting this error in Oracle

Error report -

SQL Error: ORA-00904: "Table2"."LAT": invalid identifier
00904. 00000 -  "%s: invalid identifier"

Looking for correct equivalent in Oracle.

Upvotes: 0

Views: 189

Answers (1)

Boneist
Boneist

Reputation: 23578

I would use a merge statement, something like: 

merge into table2 tgt
  using table1 src
    on (tgt.id = src.id and tgt.area = src.area)
when matched then
  update set tgt.lat = src.lat,
             tgt.lon = src.lon;

which should be more performant than the corresponding update statement:

UPDATE table2
SET    (table2.lat, table2.lon) = (SELECT table1.lat,
                                          table1.lon
                                   FROM   table1
                                   WHERE  table1.id = table2.id
                                   AND    table1.area = table2.area)
WHERE  EXISTS (SELECT NULL
               FROM   table1
               WHERE  table1.id = table2.id
               AND    table1.area = table2.area);

as the update needs to repeat the subquery twice to ensure it only updates the correct rows, whereas the merge does a join on the two tables.

Upvotes: 2

Related Questions