SQL>
SQL>
SQL> create table myTable(pid number, sales number, status varchar2(6));
Table created.
SQL> create table master_tab(pid number, sales number, status varchar2(6));
Table created.
SQL>
SQL> insert into master_tab values(1,12,'CURR');
1 row created.
SQL> insert into master_tab values(2,13,'NEW' );
1 row created.
SQL> insert into master_tab 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>
SQL> merge into master_tab 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 master_tab;
PID SALES STATUS
---------- ---------- ------
1 12 CURR
2 37
4 42 NEW
SQL>
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL> drop table master_tab;
Table dropped.
SQL>