Merge table : Merge « Table « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Table
3. Merge

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>
SQL> drop table myTable;

Table dropped.

SQL> drop table myTable2;

Table dropped.

SQL>