Re: MERGE Command

From: joel garry <joel-garry_at_home.com>
Date: Fri, 9 Jan 2009 09:50:01 -0800 (PST)
Message-ID: <121021ab-a514-4837-bad7-47eb76d91df4_at_g3g2000pre.googlegroups.com>

On Jan 7, 1: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!

Perhaps you can:

insert into yourtable (yourcolumns...)
(select yourvalues...
  from yourtablewithnewvalues
  where whateverconditions and yourcertainvalue in select (yourcertainvalue from yourtable)
/

I've done it the other way (ie, needed to create rows that didn't exist without trying to insert where they do, all within a single table), so I haven't thought your situation through, but maybe this will get you started.

jg

--
_at_home.com is bogus.
What's in a domain name?
http://www3.signonsandiego.com/stories/2009/jan/09/1b9whitman21190-internet-pioneer-cyberspat/?uniontrib

Received on Fri Jan 09 2009 - 11:50:01 CST