Oracle SQL - Data Change Command MERGE Command

Introduction

MERGE command can perform insertions, updates, and deletions in a single statement.

The MERGE command can react appropriately to the existence or nonexistence of rows.

The following code creates new tables in order to demonstrate MERGE command.

Demo

SQL>
SQL> drop table delta_tab;

Table dropped.-- from w  ww  .j a v a 2 s  . c om
SQL>
SQL> create table delta_tab
  2  ( pid number,
  3    sales number,
  4    status varchar2(6));

Table created.
SQL>
SQL> drop table master_tab;

Table dropped.
SQL>
SQL> create table master_tab
  2  (pid number, sales number, status varchar2(6));

Table created.

SQL>
SQL> insert into master_tab values(1,12,'CURR');
SQL> insert into master_tab values(2,13,'NEW' );
SQL> insert into master_tab values(3,15,'CURR');
SQL> insert into delta_tab  values(2,24,'CURR');
SQL> insert into delta_tab  values(3, 0,'OBS' );
SQL> insert into delta_tab  values(4,42,'CURR');
SQL>
SQL> select * from master_tab;

       PID      SALES STATUS
---------- ---------- ------
         1         12 CURR
         2         13 NEW
         3         15 CURR

SQL> select * from delta_tab;

       PID      SALES STATUS
---------- ---------- ------
         2         24 CURR
         3          0 OBS
         4         42 CURR
SQL>
SQL> merge into master_tab m
  2            using delta_tab 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 CURR
         4         42 NEW

SQL>

Here, in MERGE command the first three command lines set the roles of the two tables involved and the joining condition between the two tables.

Then it says what must be done when processing a row from the DELTA_TAB table if there is a matching row in the MASTER_TAB table.

It also specifies what must be done when such a matching row does not exist.

Here is what happened to MASTER_TAB table?

• The first row is not touched, because the DELTA_TAB contains no row for product 1.
• The second row is updated: the SALES value is incremented with 24, and the STATUS is set to CURR.
• The third (original) row is deleted, because after applying the UPDATE clause, the DELETE condition became TRUE.
• The fourth row is inserted, because there was no row for product 4.

• Previous
• Next