How to update a table from a another table
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
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
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.
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.
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