Re: MERGE Command

From: <mrdjmagnet_at_aol.com>
Date: Wed, 7 Jan 2009 13:08:41 -0800 (PST)
Message-ID: <05baa8aa-57b3-4867-ab5e-f5ad0ba9669a_at_t39g2000prh.googlegroups.com>

On Jan 7, 2:55 pm, ddf <orat..._at_msn.com> wrote:
> 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

Basically the app is saying that if a record with a certain value already exists, insert the new record, otherwise skip it. The table is a history table and in this part of the app we only insert if a record previously exists.

Thanks! Received on Wed Jan 07 2009 - 15:08:41 CST