Re: Bending the rules using MERGE INTO

From: Jeffrey Hunter <jhunter_at_iDevelopment.info>
Date: 18 Jul 2003 13:24:13 -0700
Message-ID: <9dfc69b4.0307181224.54343b05@posting.google.com>

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
     USING (
         SELECT 50            deptno
              , 'ENGINEERING' dname
              , 'WEXFORD'     loc
         FROM dual
     ) b
     ON (a.deptno = b.deptno)
     WHEN NOT MATCHED THEN
         INSERT VALUES (b.deptno, b.dname, b.loc)
     WHEN MATCHED THEN
         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
     USING (
         SELECT 50            deptno
              , 'ENGINEERING' dname
              , 'WEXFORD'     loc
         FROM dual
     ) b
     ON (a.deptno = b.deptno)
     WHEN NOT MATCHED THEN
         INSERT VALUES (b.deptno, b.dname, b.loc)
     WHEN MATCHED THEN
         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

Kindest regards,
-- jeff

---

Jeffrey Hunter, OCP
Senior Database Administrator
Email : jhunter_at_iDevelopment.info
Web : www.iDevelopment.info

---

jhunter_at_iDevelopment.info (Jeffrey Hunter) wrote in message news:<9dfc69b4.0307161122.31f3669e_at_posting.google.com>...
> Hello,
>
> I have a situation where I would like to use the MERGE INTO statement
> to merge literal values into a single table based on a particular
> condition. (I don't want to use PL/SQL to solve this, rather a single
> SQL statement). The query below provides an example of what I am
> trying to accomplish. I am looking for comments or suggestions if I am
> bending the rules here or is there a better way to accomplish this.
> Thanks in advance!
>
> SQL> create table test (a number primary key, b number);
>
> SQL> merge into test
> using dual on (dual.dummy is not null and test.a = 1)
> when not matched then
> insert values (1,1)
> when matched then
> 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
> using dual on (dual.dummy is not null and test.a = 1)
> when not matched then
> insert values (1,1)
> when matched then
> 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
>
>
> Regards,
> -- jeff
> -----------------------------------
> Jeffrey Hunter, OCP
> Senior Database Administrator
> Email : jhunter_at_iDevelopment.info
> Web : www.iDevelopment.info
> -----------------------------------
Received on Fri Jul 18 2003 - 15:24:13 CDT