Mega Code Archive
Merge table
<![CDATA[
SQL>
SQL> create table myTable
2 (pid number, sales number, status varchar2(6));
Table created.
SQL>
SQL> create table myTable2
2 (pid number, sales number, status varchar2(6));
Table created.
SQL>
SQL> insert into myTable2 values(1,12,'CURR');
1 row created.
SQL> insert into myTable2 values(2,13,'NEW' );
1 row created.
SQL> insert into myTable2 values(3,15,'CURR');
1 row created.
SQL>
SQL> insert into myTable values(2,24,'CURR');
1 row created.
SQL> insert into myTable values(3, 0,'OBS' );
1 row created.
SQL> insert into myTable values(4,42,'CURR');
1 row created.
SQL>
SQL> select * from myTable2;
PID SALES STATUS
---------- ---------- ------
1 12 CURR
2 13 NEW
3 15 CURR
SQL> select * from myTable;
PID SALES STATUS
---------- ---------- ------
2 24 CURR
3 0 OBS
4 42 CURR
SQL>
SQL> merge into myTable2 m
2 using myTable d
3 on (m.pid = d.pid)
4 when matched
5 then update set m.sales = m.sales+d.sales
6 , m.status = d.status
7 delete where m.status = 'OBS'
8 when not matched
9 then insert values (d.pid,d.sales,'NEW');
3 rows merged.
SQL>
SQL> select * from myTable2;
PID SALES STATUS
---------- ---------- ------
1 12 CURR
2 37 CURR
4 42 NEW
SQL>
SQL> drop table myTable;
Table dropped.
SQL> drop table myTable2;
Table dropped.
SQL>
]]>