What to do in case of sql insert if not exists?

1.2K Asked by ClareMatthews in SQL Server , Asked on Jun 6, 2024

My question is if there's an atomic insert-if-not-exist statement in Oracle Database? This is not part of ANSI SQL so each database vendor has his own way of doing this, if supported.

To qualify the statement must work under high concurrent load and never throw duplicate key errors. The statement must either succeed (row inserted) or do nothing (PK value already exists).

I've done a bit of Googling and my temporary conclusion is that Oracle does not have support for this, so the only viable route is a try-insert-catch-and-ignore-dup-key-error. Really just wanted help in either verifying or rejecting this assumption.

Answered by David Piper

In case of sql insert if not exists -

If 2 transactions try to MERGE a new value, the second one will get an ORA-00001 after the first one commits.

Session 1:

SQL> create table t1 (id number not null, constraint t1_pk primary key(id));

Table created.

SQL> merge into t1 using (select 1 as id from dual) s on (t1.id = s.id) 

     when not matched then insert (id) values (s.id);

1 row merged.

SQL>

Session 2 (waits for Session 1):

SQL> merge into t1 using (select 1 as id from dual) s on (t1.id = s.id) 

     when not matched then insert (id) values (s.id);

Session 1:

SQL> commit;

Commit complete.

SQL>

Session 2:

merge into t1 using (select 1 as id from dual) s on (t1.id = s.id)

when not matched then insert (id) values (s.id)

ERROR at line 1:

ORA-00001: unique constraint (BP.T1_PK) violated

SQL>

However, Oracle provides the IGNORE_ROW_ON_DUPKEY_INDEX hint, which will suppress the ORA-00001 and the second transaction can finish without an error and it will not insert the offending row(s).

Session 1:

SQL> insert /*+ ignore_row_on_dupkey_index(t1,t1_pk) */ into t1 values (2);

1 row created.

SQL>

Session 2 (waiting for Session 1):

SQL> insert /*+ ignore_row_on_dupkey_index(t1,t1_pk) */ into t1 values (2);

Session 1:

SQL> commit;

Commit complete.

SQL>

Session 2:

0 rows created.

SQL>

Your Answer

Answer (1)

Ranjana

To perform an INSERT operation only if a certain condition is met (e.g., the data does not already exist), you can use various strategies depending on the database system you are using. Here are some common methods for handling this situation in SQL:

1. Using INSERT IGNORE (MySQL)

In MySQL, you can use INSERT IGNORE to ignore errors caused by duplicate keys:

INSERT IGNORE INTO table_name (column1, column2)

  VALUES (value1, value2);

If a row with the same primary key or unique key already exists, the insertion will be ignored.

2. Using ON DUPLICATE KEY UPDATE (MySQL)

You can also use ON DUPLICATE KEY UPDATE to update the existing row if a duplicate key is found:

  INSERT INTO table_name (column1, column2)VALUES (value1, value2)ON DUPLICATE KEY UPDATE column1 = VALUES(column1);

3. Using MERGE (SQL Server)

In SQL Server, you can use the MERGE statement:

  MERGE INTO table_name AS targetUSING (SELECT value1 AS column1, value2 AS column2) AS sourceON target.column1 = source.column1WHEN MATCHED THEN     -- Do nothing or perform an updateWHEN NOT MATCHED THEN    INSERT (column1, column2) VALUES (source.column1, source.column2);

4. Using INSERT ON CONFLICT (PostgreSQL)

In PostgreSQL, you can use INSERT ON CONFLICT to handle conflicts:

  INSERT INTO table_name (column1, column2)VALUES (value1, value2)ON CONFLICT (column1) DO NOTHING;

5. Using INSERT IF NOT EXISTS Pattern (Standard SQL)

For databases that do not support the above syntax, you can use a combination of INSERT and a subquery to check for existence:

  INSERT INTO table_name (column1, column2)SELECT value1, value2WHERE NOT EXISTS (    SELECT 1    FROM table_name    WHERE column1 = value1);

Example for MySQL

Here's an example demonstrating the use of INSERT IGNORE and ON DUPLICATE KEY UPDATE in MySQL:

INSERT IGNORE Example:

  CREATE TABLE users (    id INT AUTO_INCREMENT PRIMARY KEY,    username VARCHAR(255) UNIQUE);INSERT IGNORE INTO users (username)VALUES ('john_doe');

ON DUPLICATE KEY UPDATE Example:

  INSERT INTO users (username)VALUES ('john_doe')ON DUPLICATE KEY UPDATE username = VALUES(username);

Example for PostgreSQL

Here's an example demonstrating the use of INSERT ON CONFLICT in PostgreSQL:

  CREATE TABLE users (    id SERIAL PRIMARY KEY,    username VARCHAR(255) UNIQUE);INSERT INTO users (username)VALUES ('john_doe')ON CONFLICT (username) DO NOTHING;

6 Months