Re: MERGE Command

From: ddf <oratune_at_msn.com>
Date: Wed, 7 Jan 2009 13:22:26 -0800 (PST)
Message-ID: <eb7e4c86-bef5-4db9-b3e1-7fa0e6d115a9_at_o40g2000prn.googlegroups.com>

On Jan 7, 3:08 pm, mrdjmag..._at_aol.com wrote:
> 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!- Hide quoted text -
>
> - Show quoted text -

MERGE INTO ... WHEN MATCHED ... won't work for that. You'll probably need a procedure or an anonymous PL/SQL block to perform that operation.

David Fitzjarrell Received on Wed Jan 07 2009 - 15:22:26 CST