Re: MERGE Command

From: ddf <oratune_at_msn.com>
Date: Wed, 7 Jan 2009 12:55:46 -0800 (PST)
Message-ID: <cbb06fb7-1ea8-415d-b590-4f618d257a4f_at_f40g2000pri.googlegroups.com>

On Jan 7, 2:39 pm, mrdjmag..._at_aol.com wrote:
> Been looking all over for this answer:
>
> MERGE INTO x USING DUAL ON (x.c4 = 'A')
> WHEN MATCHED THEN
>   INSERT VALUES (0,0,0,'0');
>
> ERROR at line 3:
> ORA-00905: missing keyword
>
> I want to skip the whole "SELECT COUNT(*) blah, blah, blah" to check
> for record existence.   A simple MERGE with a MATCHED clause should do
> it.   This is an example I'm using.......
>
> What simple thing am I missing?

MATCHED values are to be updated, not inserted:

SQL> create table x(

  2          c1 number,
  3          c2 number,
  4          c3 number,
  5          c4 varchar2(5)

  6 );

Table created.

SQL>
SQL> MERGE INTO x USING (select 'A' c4 from DUAL) d ON (x.c4 = d.c4)   2 WHEN MATCHED THEN
  3 INSERT VALUES (0,0,0,'A');
  INSERT VALUES (0,0,0,'A')
  *
ERROR at line 3:
ORA-00905: missing keyword

SQL> Making this NOT MATCHED:

SQL> MERGE INTO x USING (select 'A' c4 from DUAL) d ON (x.c4 = d.c4)   2 WHEN NOT MATCHED THEN
  3 INSERT VALUES (0,0,0,'A'); 1 row merged.

SQL> It now works, but I expect it doesn't function as you intended. What is the purpose of attempting to insert records on a match in a MERGE statement?

David Fitzjarrell Received on Wed Jan 07 2009 - 14:55:46 CST