René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

merge [Oracle SQL]

merge [ hints ] into table-name-1 | view-name-1 [ alias-1 ]
using table-name-2 | view-name-2 | subquery [ alias-2 ]
on ( condition )
[ merge-update-clause ] [ merge-insert-clause ] [ error-logging-clause ];
create table table_dest (
   id  number primary key,
   txt varchar2(20)
);

insert into table_dest values (1,'one');
insert into table_dest values (3,'three');
insert into table_dest values (5,'five');

commit;
create table table_source (
   id  number primary key,
   txt varchar2(20)
);

insert into table_source values (2,'TWO');
insert into table_source values (3,'THREE');

commit;
merge into 
  table_dest d
using
  table_source s
on 
  (s.id = d.id)
when     matched then update set d.txt = s.txt
when not matched then insert (id, txt) values (s.id, s.txt);
select * from table_dest;
        ID TXT
---------- --------------------
         1 one
         3 THREE
         5 five
         2 TWO
Note: It is not possible to update a field in the destination table that is used to merge with the source table.