I have one table A has column (id, field_1,field_2), and another table B has column (id,field_2)

Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. So how to implement that ? BTW I am using oracle


  • Do both the tables contain same number of records?
    – ujjwalesri
    Commented May 30, 2011 at 10:14

4 Answers 4


An alternative approach to the co-related subquery (suggested by Kerri) would be to use the MERGE statement, which might be more efficient than the subselect (which can only be verified by looking at the execution plan of both statements).

MERGE INTO table_b 
  SELECT id,
  FROM table_a
) ta ON (ta.id = table_b.id)
    SET table_b.field_2 = ta.field_2
  • 2
    The only restriction to the MERGE statement is that you cannot update the column on which it is joined, i.e. you cannot update the column used in the ON clause. Commented Nov 4, 2015 at 7:08
  • This worked for me, updated 2.5 million rows vs. attempting the sub-query method which ran for maybe 45 minutes before erroring with ORA-01555: snapshot too old
    – helmy
    Commented Jan 17, 2018 at 2:13

Not exactly sure what you're after on this one, but this should work as a one-off, or continuous via a scheduled job:

UPDATE table_a a
   SET field_2 = ( SELECT field_2
                     FROM table_b b
                    WHERE b.id = a.id )

Now, each time the above is executed, it will do it across all rows in the table. If this is something you need to do all the time, I would suggest something else, but for a one-off or very small tables it should be sufficient.


I have done this successfully using one table in user1 from another table in user2:

update user1.table1 a
set a.field1 = (
  select b.field1
  from user2.table2 b
  where a.field1=b.field1
where <condition for update user1.table1>

You could create a trigger on tableB that updates tableA every time field_2 on tableB is updated. Check here for more info on creating triggers - http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#BABCIBBJ

  • Not relevant answer
    – CleanBold
    Commented Aug 10, 2016 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.