MERGE INTO without WHEN MATCHED, possible?

From: <gq437x_at_yahoo.de>
Date: 18 Sep 2002 16:52:17 +0200
Message-ID: <lgznufuz8e.fsf@mu.biosolveit.local>

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 - 09:52:17 CDT