Insert if Not Exists Oracle

sql

conditional-inserts

oracle-sql

exception-handling

byNikita Barsukov·Sep 7, 2024

To ward off undesired duplicates in Oracle, seize the power of the MERGE statement:

MERGE INTO my_table t USING (SELECT 1 as id, 'Sample data' as data FROM dual) s
ON (t.id = s.id)
WHEN NOT MATCHED THEN INSERT (id, data) VALUES (s.id, s.data);

This MERGE command seeks a matching row in my_table using the ON clause. If the search yields no fruit (WHEN NOT MATCHED), it plants the new data in the soil. This safeguard is synonymous with Oracle's signature upsert provision.

Leveraging Oracle: conditional insertion

PL/SQL with exception handling

In mission-critical scenarios or crowded environments (high contention), pre-checking row existence with PL/SQL enhances performance.

/* Give the key, get the door. Duplicate? The door stays put */
BEGIN
   INSERT INTO my_table (id, data)
   SELECT 2, 'Another sample' FROM dual
   WHERE NOT EXISTS (SELECT 1 FROM my_table WHERE id = 2);

EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      NULL; // She said: "Dup, Dup, Dup, Dup. I'm ignoring you!";
END;

The above nail-biting sequence stirs an insertion attempt. Should a surprising duplicate key error (DUP_VAL_ON_INDEX) dare to surface, it parachutes to safety courtesy of the exception handler.

IGNORE_ROW_ON_DUPKEY_INDEX go home (only if you are 11.2.0.1)

Delve into the age of Oracle 11.2.0.1 and beyond. The IGNORE_ROW_ON_DUPKEY_INDEX hint offers a smooth ride:

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(my_table, my_table_pk) */ INTO my_table (id, data) 
VALUES (3, 'Unique data'); // Every snowflake thinks it's unique.

Ensure my_table_pk refers to your primary key's index for my_table. The hint steps aside if the data already has a spot on the table.

Conditional logic for Oracle SQL? Done.

Revisiting MERGE with a twist

Clearly, the performance of our MERGE friend solely depends on its usage.

MERGE INTO my_table t USING (
    SELECT 4 as id, 'New data' as data FROM dual
) s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.data = s.data
WHEN NOT MATCHED THEN INSERT (id, data) VALUES (s.id, s.data);

Voila! An UPDATE and INSERT combo, the WHEN MATCHED clause, steps into the spotlight for any existing rows. Yup! Your fingers just stumbled upon the secret key to efficiently tackling upsert operations.

LEFT OUTER JOIN join the club

Secretly yearning for an alternative route to conditional inserts? Embrace the warm company of the LEFT OUTER JOIN:

INSERT INTO my_table (id, data) 
SELECT 5, 'Exclusive data' FROM dual
WHERE NOT EXISTS (
    SELECT 1 FROM my_table t 
    LEFT OUTER JOIN dual d ON t.id = 5 
    WHERE t.id IS NOT NULL
); /* Left Outer Join says: "Come to the party. Don't worry, I got your back!" */

Like a wise shaman, the outer join foretells if an id nestles in my_table. Should the foretold 'id' be absent... poof! The NULL gives your WHERE clause the magic green light.

Conditional inserts: Play smart

TESTING IS KING!

Are you contemplating ignoring this? Rethink! Perform in-depth testing, digest the results. Be mindful of the index you opt for, table size, database load, and concurrency levels. Recreate those conditions. Test again. Remember the golden rule: TEST, TEST, and TEST!

Double-edged sword: concurrency conflicts

Watch out when dealing with concurrent transactions coupled with MERGE operations, especially non-atomic ones. Seek refuge in SERIALIZABLE isolation or consider employing locks for a clean state.