Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


share|improve this question
Do both the tables contain same number of records? – ujjwalesri May 30 '11 at 10:14

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 ( =
    SET table_b.field_2 = ta.field_2

That is essentially the Oracle "way" for the join solution suggested by jcolebrand:

share|improve this answer
+1 Invaluable information. Coming from SQL Server, I tried the approach @jcoleband suggested first. This works like a charm! – Yuck Nov 23 '12 at 19:18
Sorry, I guess I was too pissed off to realize I was out of line. I'll delete it. – Radu Murzea Sep 6 '13 at 9:48
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

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 = )

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.

share|improve this answer

An alternate suggestion to the answer by Kerri Shotts given for feedback on my own technique.

   SET a.field_2 = b.field_2
FROM table_a a
INNER JOIN table_b b
  ON =

I feel this is a little more readable. ... of course, all this is only true if and refer to the same values. One would expect it's what you wanted.

share|improve this answer
Oracle does not support JOINs in an UPDATE statement so it's not applicable for the OP – a_horse_with_no_name May 31 '11 at 8:03
I was not aware of that. Thanks! – jcolebrand May 31 '11 at 13:29
@a_horse_with_no_name: Oracle does not directly support the JOIN keyword in an UPDATE, but it allows UPDATE involving JOIN operations between table in at least 2 different ways, look at the accepted answer here:… – alessiop86 Apr 29 '14 at 13:18
@mark: yes I know that. But the syntax given in this answer is not valid for Oracle. – a_horse_with_no_name Apr 29 '14 at 13:31

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>
share|improve this answer

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 -

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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