Re: MERGE Command
Date: Wed, 7 Jan 2009 12:55:46 -0800 (PST)
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)
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