Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

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

5 Answers

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.

share|improve this answer
add comment

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

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
add comment

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

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

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

share|improve this answer
7  
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: stackoverflow.com/questions/2446764/… –  mark Apr 29 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 at 13:31
add comment

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

share|improve this answer
add comment

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
add comment

Your Answer

 
discard

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.