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;
Recent Comments