Archive

Posts Tagged ‘merge’

Oracle PL/SQL – Merge table

Thursday, 20 January, 2011 2 comments
clear;
create table myTable
(pid number, sales number, status varchar2(6));
 create table myTable2
(pid number, sales number, status varchar2(6));

 insert into myTable  values(2,24,'CURR');
 insert into myTable  values(3, 0,'OBS' );
 insert into myTable  values(4,42,'CURR');
 insert into myTable  values(6,56,'C44URR');


 insert into myTable2 values(1,12,'CURR');
 insert into myTable2 values(2,13,'NEW' );
 insert into myTable2 values(3,15,'CURR');

select * from myTable;
select 't2' from dual;

merge into myTable2 m
using myTable d
on (m.pid = d.pid)
when matched then
  update
     set m.sales = d.sales, m.status = d.status 
when not matched then  
   insert values (d.pid, d.sales, d.status);
  
 delete myTable2 where pid in (select m.pid from myTable2 m where m.pid not in (select pid from myTable));

 select * from myTable order by pid;
 select * from myTable2 order by pid;

 drop table myTable;
 drop table myTable2;
Advertisements
Categories: PL/SQL Tags: