Re: MERGE INTO without WHEN MATCHED, possible?

From: Richard Kuhler <noone_at_nowhere.com>
Date: Thu, 19 Sep 2002 00:52:52 GMT
Message-ID: <Ed9i9.43156$U7.15472405@twister.socal.rr.com>

I don't see any way to noop the WHEN MATCHED clause. However, won't ...

INSERT ... 
SELECT ... 
WHERE ... 

  AND NOT EXISTS ... do what you want?

Richard

gq437x_at_yahoo.de wrote:
>
> I try to insert a list of rows, where some rows are
> already present in the table and should not be
> reinserted, into a table with the MERGE INTO
> statement using the C-API.
> The primary key (ID) for that table is generated
> using a sequence and a trigger.
>
> Oracle complains
> ORA-00905: missing keyword
> when the WHEN MATCHED THEN part is missing.
>
> Is it allowed to leave the WHEN MATCHED part out?
> Or if not, is it possible fill it with some harmless and fast noop?
>
> 1 MERGE INTO
> 2 USING ( SELECT field1, field2 FROM tableA
> 3 WHERE a.field1 = 'y' and a.field2 = 'YYY' ) a
> 4 ON ( a.field1 = 'y' and a.field2 = 'YYY' )
> 5 WHEN NOT MATCHED THEN INSERT (field1,field2 )
> 6* VALUES( 'y', 'YYY' )
> SQL-PLUS>/
> VALUES( 'y', 'YYY' )
> *
> ERROR at line 6:
> ORA-00905: missing keyword
>
> -- ID is created by trigger BEFORE INSERT from a sequence
> CREATE TABLE tableA (
> ID NUMBER NOT NULL,
> field1 VARCHAR2(64) not null,
> field2 VARCHAR2(64) not null,
> CONSTRAINT pk_tableA PRIMARY KEY (ID)
> )
> /
>
> CREATE UNIQUE INDEX idx_fk_tableA ON tableA(field1,field2)
> /
>
> INSERT INTO tableA(field1, field2)
> values( 'X' , 'XXX' )
> /
>
> --
> -- then MERGE INTO with values field1 == 'y' and field2 == 'YYY'
> -- as shown above
>
> --------------- PROBLEM
> -- for C-API Program
> -- values are :1 == :3 == :5 and :2 == :4 == :6
> -- so all values are given three times
> --
> MERGE INTO tableA
> USING ( select field1, field2 from tableA
> where a.field1 = :1 and a.field2 = :2 ) a
> ON ( a.field1 = :3 and a.field2 = :4 )
> --
> -- WHEN MATCHED THEN UPDATE
> -- ?? I don't want to update the table, if that row exists.
> -- ?? I'm only intrested in INSERT when not matched.
> --
> WHEN NOT MATCHED THEN INSERT ( field1, field2 )
> VALUES( :5, :6)
>
> --
> Volker Apelt
Received on Wed Sep 18 2002 - 19:52:52 CDT