Warning: Creating default object from empty value in /home/customer/www/gavinsoorma.com/public_html/wp-content/themes/specular/admin/inc/class.redux_filesystem.php on line 29
10g MERGE statement | Oracle DBA – Tips and Techniques
News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

10g MERGE statement

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 0 Comments

The MERGE statement (UPSERT, combination of  INSERT/UPDATE) was introduced in Oracle9i.

In 10g it has been enhanced to include conditional INSERT/UPDATE/DELETE clauses as part
of the MERGE statement.

Let us use the EMP and BONUS table in SCOTT schema to illustrate the use of this statement

EMP Table has got 14 rows

SQL>  select * from emp;

EMPNO ENAME           JOB          MGR DOJ              SAL       COMM  DEPTNO
----- --------------- ---------- ----- --------- ---------- ---------- -------
 7369 SMITH           CLERK       7902 17-DEC-80       1200                 20
 7499 ALLEN           SALESMAN    7698 20-FEB-81       2400       2700      30
 7521 WARD            SALESMAN    7698 22-FEB-81       1875       2375      30
 7566 JONES           MANAGER     7839 02-APR-81     4462.5                 20
 7654 MARTIN          SALESMAN    7698 28-SEP-81       1875       3275      30
7698 BLAKE           MANAGER     7839 01-MAY-81       4275                 30
 7782 CLARK           MANAGER     7839 09-JUN-81       3675                 10
 7788 SCOTT           ANALYST     7566 19-APR-87       4500                 20
 7839 KING            PRESIDENT        17-NOV-81       7500                 10
 7844 TURNER          SALESMAN    7698 08-SEP-81       2250       2250      30
 7876 ADAMS           CLERK       7788 23-MAY-87       1650                 20
 7900 JAMES           CLERK       7698 03-DEC-81       1425                 30
 7902 FORD            ANALYST     7566 03-DEC-81       4500                 20
 7934 MILLER          CLERK       7782 23-JAN-82       1950                 10



Bonus table has got  5 rows

SQL> select * from bonus;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLERK           1200       1400
TURNER     SALESMAN        2250       1400
MILLER     CLERK           1950       1400
MARTIN     SALESMAN        1875       1400
JAMES      CLERK           1425       1400



Perform a conditional INSERT and UPDATE – an UPSERT

If records exist then update the COMM column to COMM*2

If records do not exist then INSERT new records and give them COMM value of 150


SQL>

MERGE into bonus b
using (select ename, job, sal, comm from emp)  s
on (b.ename = s.ename)
when matched then UPDATE set b.comm = b.comm*2
when not matched then INSERT (b.ename,b.job, b.sal, b.comm)
values (s.ename,s.job,s.sal,150);

SQL> select * from bonus;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLERK           1200       2800
TURNER     SALESMAN        2250       2800
MILLER     CLERK           1950       2800
MARTIN     SALESMAN        1875       2800
JAMES      CLERK           1425       2800
BLAKE      MANAGER         4275        150
FORD       ANALYST         4500        150
ALLEN      SALESMAN        2400        150
WARD       SALESMAN        1875        150
CLARK      MANAGER         3675        150
ADAMS      CLERK           1650        150
SCOTT      ANALYST         4500        150
KING       PRESIDENT       7500        150
JONES      MANAGER       4462.5        150
14 rows selected.



Note :

5 rows were updated as they already existed

9 new rows were inserted as they did not exist in the BONUS table


Records in bold characters are all new records inserted and assigned a default value of 150

Existing records were updated to have the COMM value doubled


Perform a conditional UPDATE

Management now decides to double the bonus for SALESMAN ….the BONUS table needs to be updated
to reflect this change in the EMP table


SQL>

MERGE into bonus b
using (select ename, job, sal, comm from emp where job='SALESMAN')  s
on (b.ename = s.ename)
when matched then UPDATE set b.comm = b.comm*2
when not matched then INSERT (b.ename,b.job, b.sal, b.comm)
values (s.ename,s.job,s.sal,s.comm);


Note: 4 rows GOT MERGED (updated)


SQL> select * from bonus;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLERK           1200       2800
TURNER     SALESMAN        2250       5600
MILLER     CLERK           1950       2800
MARTIN     SALESMAN        1875       5600
JAMES      CLERK           1425       2800
BLAKE      MANAGER         4275        150
FORD       ANALYST         4500        150
ALLEN      SALESMAN        2400        300
WARD       SALESMAN        1875        300
CLARK      MANAGER         3675        150
ADAMS      CLERK           1650        150
SCOTT      ANALYST         4500        150
KING       PRESIDENT       7500        150
JONES      MANAGER       4462.5        150

14 rows selected.


Perform a conditional DELETE

Management now decides that the COMM value in the BONUS table needs to be the same as the
COMM value in the EMP table.

Also, only employees with job title SALESMAN are entitled to a bonus and hence the BONUS table
should only have records for the job title SALESMAN


SQL>

MERGE into bonus b
using (select ename, job, sal, comm from emp)  s
on (b.ename = s.ename)
when matched then UPDATE set b.comm = s.comm
delete where b.job  'SALESMAN'
when not matched then INSERT (b.ename,b.job, b.sal, b.comm)
values (s.ename,s.job,s.sal,s.comm);
14 rows merged.

SQL> commit;

Commit complete.

SQL> select * from bonus;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
TURNER     SALESMAN        2250       2250
MARTIN     SALESMAN        1875       3275
ALLEN      SALESMAN        2400       2700
WARD       SALESMAN        1875       2375

 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *