Merge into table with composite primary key

Question

I'm trying to insert some entries into my table and do nothing when the entry already exists. My primary key consists of more than one column (event_timestamp, device_id, path, message_id).

When I try something like following statement in JDBC:

MERGE INTO events dest
                    USING ( SELECT event_timestamp, device_id, path, message_id from events) src
                    ON (dest.event_timestamp = src.event_timestamp
                    and dest.device_id = src.device_id
                    and dest.path = src.path
                    and dest.message_id = src.message_id)
                    WHEN NOT MATCHED THEN
                    INSERT (event_timestamp, device_id, path, message_id, text, direction, speed, net, gross)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

Nothing gets inserted.

With a normal statement like :

INSERT (event_timestamp, device_id, path, message_id, text, direction, speed, net, gross) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

everything is inserted without problems.

I also tried changing the select statement to select from DUAL instead of events like :

MERGE INTO events dest
                    "USING ( SELECT event_timestamp, device_id, path, message_id from DUAL) src
                    ON (dest.event_timestamp = src.event_timestamp
                    and dest.device_id = src.device_id 
                    and dest.path = src.path
                    and dest.message_id = src.message_id)
                    WHEN NOT MATCHED THEN
                    INSERT (event_timestamp, device_id, path, message_id, text, direction, speed, net, gross)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

But then I get the exception: ORA-00904: invalid identifier

What can I do to make it work?

`WHEN MATCHED THEN` try doing an `UPDATE`, this will ensure matching records do exist or not — Jacob, Dec 07 '18 at 11:06
Is `event_timestamp` is a date or timestamp column? If so ensure to insert unique records. — Jacob, Dec 07 '18 at 11:08
@Jåcob: Thanks for commenting. I get following exception when I try to do an update on any of the referenced columns `ORA-38104: Columns referenced in the ON Clause cannot be updated`. `event_timestamp` is a timestamp column. I'm trying to ensure I'm inserting unique records by checking the composite (event_timestamp, device_id, path, message_id). — ScalaNewbie, Dec 07 '18 at 11:15
It would be easier to understand your SQL if you posted "plain" SQL rather than String concatenation in Java — , Dec 07 '18 at 11:50

Kaushik Nayak · Accepted Answer · 2018-12-07T11:50:14.930

The new values you want to pass should be part of SELECT FROM dual in the USING clause

MERGE INTO events dest USING (
     SELECT ? as event_timestamp,
            ? as device_id,
            ? as path,
            ? as message_id
     FROM dual
)
src ON (
      dest.event_timestamp = src.event_timestamp 
      AND dest.device_id = src.device_id 
      AND dest.path = src.path 
      AND dest.message_id = src.message_id
)
WHEN NOT MATCHED THEN INSERT (
     event_timestamp,
     device_id,
     path,
     message_id,
     text,
     direction,
     speed,
     net,
     gross ) VALUES (?,?,?,?,?,?,?,?,?);

A plain INSERT INTO .. SELECT like this can also be used.

INSERT INTO events (
     event_timestamp,
     device_id,
     path,
     message_id,
     text,
     direction,
     speed,
     net,
     gross ) 
SELECT ?,?,?,?,?,?,?,?,? FROM DUAL
WHERE NOT EXISTS ( select 1 from events d WHERE  
          d.event_timestamp  = ? 
      AND d.device_id        = ?
      AND d.path             = ?
      AND d.message_id       = ?
      );

Demo

Merge into table with composite primary key

1 Answers1