Re: Bending the rules using MERGE INTO

Hey Mate:

Just where do you get off calling yourself a Senior Anything with a question like that? Obviously you can go through the bother of a merge statement if you want. I find it far simpler to use a series of insert and update statements (See the SQL Reference Guide for pointers on that). You may want to consider using C:\oracle\ora9i\BIN\SQLPLUSW.EXE to run this by the way. Based on the scenario you mentioned I have knocked out some of the coding for you:

insert into test (a, b) values (1, 1);
update test set b = 2;

Cheers,
- Simon Adebisi
Sr. Oracle Engineer

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 Wed Jul 16 2003 - 22:54:23 CDT