28

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

Thanks

  • Do both the tables contain same number of records? – ujjwalesri May 30 '11 at 10:14
29

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 
USING 
(
  SELECT id,
         field_2
  FROM table_a
) ta ON (ta.id = table_b.id)
WHEN MATCHED THEN UPDATE 
    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. – Lalit Kumar B Nov 4 '15 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 Jan 17 '18 at 2:13
15

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.

2

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>
1

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 Aug 10 '16 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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