MERGE Command

[mrdjm...@aol.com]

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?

[ddf]

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

[mrdjm...@aol.com]

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!

[pkl]

try to read this:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9016.htm#sthref9090

[ddf]

> 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

[joel garry]

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
--
@home.com is bogus.
What's in a domain name?
http://www3.signonsandiego.com/stories/2009/jan/09/1b9whitman21190-internet-pioneer-cyberspat/?uniontrib