DBA Tips Archive for Oracle

  


Using MERGE INTO to upsert Literal Values

by Jeff Hunter, Sr. Database Administrator

This is more of an observation than an article on using the MERGE INTO statement. I posted this to an Oracle news group several months ago and never received much in the way of feedback. In this posting, I was looking for suggestions on how to use the MERGE INTO statement to merge literal values into a single table based on a particular condition. (I didn't want to use PL/SQL logic to solve this, but rather a single SQL statement.)

The query below provides an example of what I am trying to accomplish. In this example, I want to insert the values (a=1, b=1) into the test table if the primary key (a = 1) does not exist. If the primary key (a =1) does exist, I want to update column b to the value of two. Here is the example:

SQL> create table test (a number primary key, b number);

SQL> merge into test
  2  using dual on (dual.dummy is not null and test.a = 1)
  3  when not matched then
  4  insert values (1,1)
  5  when matched then
  6  update set test.b = 2;

1 row merged.


Execution Plan
----------------------------------------------------------
0 MERGE STATEMENT Optimizer=CHOOSE (Cost=5382720 Card=5470730368 Bytes=142238989568)

1 0 MERGE OF 'TEST'
2 1 VIEW
3 2 NESTED LOOPS (OUTER) (Cost=8176 Card=8168 Bytes=285880)
4 3 TABLE ACCESS (FULL) OF 'DUAL' (Cost=8 Card=8168 Bytes=16336)
5 3 VIEW
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=1 Bytes=33)
7 6 INDEX (RANGE SCAN) OF 'SYS_C007729' (UNIQUE) (Cost=1 Card=1)


SQL> select * from test;

    A     B
----- -----
    1     1


SQL> merge into test
  2  using dual on (dual.dummy is not null and test.a = 1)
  3  when not matched then
  4  insert values (1,1)
  5  when matched then
  6  update set test.b = 2;

1 row merged.


Execution Plan
----------------------------------------------------------
0 MERGE STATEMENT Optimizer=CHOOSE (Cost=5382720 Card=5470730368 Bytes=142238989568)
1 0 MERGE OF 'TEST'
2 1 VIEW
3 2 NESTED LOOPS (OUTER) (Cost=8176 Card=8168 Bytes=285880)
4 3 TABLE ACCESS (FULL) OF 'DUAL' (Cost=8 Card=8168 Bytes=16336)
5 3 VIEW
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=1 Bytes=33)
7 6 INDEX (RANGE SCAN) OF 'SYS_C007729' (UNIQUE) (Cost=1 Card=1)


SQL> select * from test;

    A     B
----- -----
    1     2


I came up with a more elegant SQL statement using MERGE to perform the upsert.

The following example will MERGE literal values (not values from another table) using one SQL statement into a table called DEPT. Prior to Oracle9i, this would need to be accomplished using PL/SQL, but with Oracle9i's MERGE command, this can all be written within one SQL statement. Keep in mind that this example will start with a pretty clean DEPT table (DEPTNO 10 - 40). I will then be merging in a DEPTNO of 50. The first time the MERGE runs, it will insert the new DEPTNO of 50. The second MERGE example will update the record for DEPTNO 50 since it already exists.

SQL> SELECT * FROM dept;

DEPTNO     DNAME           LOC
---------- --------------- -----------
10         ACCOUNTING      NEW YORK
20         RESEARCH DEV    DALLAS
30         SALES           CHICAGO
40         OPERATIONS      BOSTON


SQL> MERGE INTO dept a
  2  USING (
  3  SELECT 50 deptno
  4  , 'ENGINEERING' dname
  5  , 'WEXFORD' loc
  6  FROM dual
  7  ) b
  8  ON (a.deptno = b.deptno)
  9  WHEN NOT MATCHED THEN
 10  INSERT VALUES (b.deptno, b.dname, b.loc)
 11  WHEN MATCHED THEN
 12  UPDATE SET a.loc = 'WEXFORD, PA';

1 row merged.


SQL> SELECT * FROM dept;

DEPTNO     DNAME           LOC
---------- --------------- -----------
10         ACCOUNTING      NEW YORK
20         RESEARCH        DEV DALLAS
30         SALES           CHICAGO
40         OPERATIONS      BOSTON
50         ENGINEERING     WEXFORD


SQL> MERGE INTO dept a
  2  USING (
  3  SELECT 50 deptno
  4  , 'ENGINEERING' dname
  5  , 'WEXFORD' loc
  6  FROM dual
  7  ) b
  8  ON (a.deptno = b.deptno)
  9  WHEN NOT MATCHED THEN
 10  INSERT VALUES (b.deptno, b.dname, b.loc)
 11  WHEN MATCHED THEN
 12  UPDATE SET a.loc = 'WEXFORD, PA';

1 row merged.


SQL> SELECT * FROM dept;

DEPTNO     DNAME           LOC
---------- --------------- -----------
10         ACCOUNTING      NEW YORK
20         RESEARCH DEV    DALLAS
30         SALES           CHICAGO
40         OPERATIONS      BOSTON
50         ENGINEERING     WEXFORD, PA


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Monday, 02-Jun-2003 00:00:00 EDT
Page Count: 41519