Merge two tables when matched or not matched : Merge « Table « Oracle PL/SQL Tutorial

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

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>