Update from a database to another, with conditions

Question

I'm trying to make an update from a database to another with conditions :

UPDATE table1
SET field1 = db2.field1, field2 = db2.field2...
FROM table1@database2 AS db2
WHERE db2.fieldaaa=table1.fieldaaa
AND db2.fieldbbb=table1.fieldbbb

Also tried with :

SET table1.field1 = db2.field1, table1.field2 = db2.field2...

this is my error message : 00933. 00000 - "SQL command not properly ended"

Can someone help me please ?

There is no Update-Join in Oracle. Merge is possible in theory, but if the other database is at a different physical location, I would not advise to do that. Use a cursor in combination with `bulk collect into` to select the data from the database link, then use `forall` to do the updates in your database. — Thomas Tschernich, Sep 05 '14 at 12:50

score 0 · Accepted Answer · edited May 23 '17 at 12:21

Based on answer from here. Hope it helps!

update 
  table1 t1
set
  (
     t1.field1 , 
     t1.field2 
      ) = (
    select
        t2.field1, 
        t2.field2
    from
        table1@database2  t2
    where
       t2.fieldaaa=t1.fieldaaa
       AND t2.fieldbbb=t1.fieldbbb
     );

Note: To access tables from another database, ensure you have the DBLink, permissions and synonymns in place.

Update from a database to another, with conditions

1 Answers1