Compare two tables in Oracle

Question

I have two tables in oracle 10g (table1, table2), with the same columns (A, B, C), but different data, and I want to compare the column A and B from table1 with column And B from table2, if A and B are similar I need to update the column C of table1 with the column C from table2, if they are not similar, I just insert the new row, what should I use?

I tried with normal SQL code, didn't work when I have more than 1 similar row, what should I use to go through the whole tables?

Answer 1

You can use PL/SQL for this which will give you any flexibility to do. The format of PL/SQL will be like below:

declare 
cursor a is select * from table1; 
cursor b is select * from table2;

Begin 
For i in a 
Loop 
    for j in b 
     loop 
      if i.A=j.A & i.B=j.B then 
          ------DML operation 
     end loop; 
end Loop;

end;

Answer 2

(Posted on behalf of the OP).

Solution number1: Merge statement

merge into table1 A
using table2 B
on (A.A = B.A and A.B = B.B)
when matched then
update set A.C = B.C
when not matched then
insert (A,B,C) values(B.A,B.B,B.C);

Solution number2: cursor and for loop

cursor cur is select * from table2;
Begin

For rec in cur Loop

Update table1 Set c = rec.c Where a = rec.a and b= rec.b;

If sql%rowcount = 0 then 
insert into table1(a,b,c) values(rec.a,rec.b,rec.c); 


End if;

End loop;
end;

Answer 3

You can use merge statement insdie a loop.

Or simple update\insert in a loop like this example:

Begin

For rec in (select * from table2)
Loop

Update table1
Set c = rec.c
Where a = rec.a and b= rec.b;

If sql%rowcount = 0 then
-- some insert
...
End if;

End loop;

End;