Oracle: how to INSERT if a row doesn't exist

Question

What is the easiest way to INSERT a row if it doesn't exist, in PL/SQL (oracle)?

I want something like:

IF NOT EXISTS (SELECT * FROM table WHERE name = 'jonny') THEN
  INSERT INTO table VALUES ("jonny", null);
END IF;

But it's not working.

Note: this table has 2 fields, say, name and age. But only name is PK.

Answer 1

INSERT INTO table
SELECT 'jonny', NULL
  FROM dual -- Not Oracle? No need for dual, drop that line
 WHERE NOT EXISTS (SELECT NULL -- canonical way, but you can select
                               -- anything as EXISTS only checks existence
                     FROM table
                    WHERE name = 'jonny'
                  )

Answer 2

Assuming you are on 10g, you can also use the MERGE statement. This allows you to insert the row if it doesn't exist and ignore the row if it does exist. People tend to think of MERGE when they want to do an "upsert" (INSERT if the row doesn't exist and UPDATE if the row does exist) but the UPDATE part is optional now so it can also be used here.

SQL> create table foo (
  2    name varchar2(10) primary key,
  3    age  number
  4  );

Table created.

SQL> ed
Wrote file afiedt.buf

  1  merge into foo a
  2    using (select 'johnny' name, null age from dual) b
  3       on (a.name = b.name)
  4   when not matched then
  5    insert( name, age)
  6*    values( b.name, b.age)
SQL> /

1 row merged.

SQL> /

0 rows merged.

SQL> select * from foo;

NAME              AGE
---------- ----------
johnny

Answer 3

If name is a PK, then just insert and catch the error. The reason to do this rather than any check is that it will work even with multiple clients inserting at the same time. If you check and then insert, you have to hold a lock during that time, or expect the error anyway.

The code for this would be something like

BEGIN
  INSERT INTO table( name, age )
    VALUES( 'johnny', null );
EXCEPTION
  WHEN dup_val_on_index
  THEN
    NULL; -- Intentionally ignore duplicates
END;

Answer 4

I found the examples a bit tricky to follow for the situation where you want to ensure a row exists in the destination table (especially when you have two columns as the primary key), but the primary key might not exist there at all so there's nothing to select.

This is what worked for me:

MERGE INTO table1 D
    USING (
        -- These are the row(s) you want to insert.
        SELECT 
        'val1' AS FIELD_A,
        'val2' AS FIELD_B
        FROM DUAL

    ) S ON (
        -- This is the criteria to find the above row(s) in the
        -- destination table.  S refers to the rows in the SELECT
        -- statement above, D refers to the destination table.
        D.FIELD_A = S.FIELD_A
        AND D.FIELD_B = S.FIELD_B
    )

    -- This is the INSERT statement to run for each row that
    -- doesn't exist in the destination table.
    WHEN NOT MATCHED THEN INSERT (
        FIELD_A,
        FIELD_B,
        FIELD_C
    ) VALUES (
        S.FIELD_A,
        S.FIELD_B,
        'val3'
    )

The key points are:

• The SELECT statement inside the USING block must always return rows. If there are no rows returned from this query, no rows will be inserted or updated. Here I select from DUAL so there will always be exactly one row.
• The ON condition is what sets the criteria for matching rows. If ON does not have a match then the INSERT statement is run.
• You can also add a WHEN MATCHED THEN UPDATE clause if you want more control over the updates too.

Answer 5

Using parts of @benoit answer, I will use this:

DECLARE
    varTmp NUMBER:=0;
BEGIN
    -- checks
    SELECT nvl((SELECT 1 FROM table WHERE name = 'john'), 0) INTO varTmp FROM dual;

    -- insert
    IF (varTmp = 1) THEN
        INSERT INTO table (john, null)
    END IF;

END;

Sorry for I don't use any full given answer, but I need IF check because my code is much more complex than this example table with name and age fields. I need a very clear code. Well thanks, I learned a lot! I'll accept @benoit answer.

Answer 6

In addition to the perfect and valid answers given so far, there is also the ignore_row_on_dupkey_index hint you might want to use:

create table tq84_a (
  name varchar2 (20) primary key,
  age  number
);

insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values ('Johnny',   77);
insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values ('Pete'  ,   28);
insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values ('Sue'   ,   35);
insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values ('Johnny', null);

select * from tq84_a;

The hint is described on Tahiti.

Answer 7

you can use this syntax:

INSERT INTO table_name ( name, age )
select  'jonny', 18 from dual
where not exists(select 1 from table_name where name = 'jonny');

if its open an pop for asking as "enter substitution variable" then use this before the above queries:

set define off;
INSERT INTO table_name ( name, age )
select  'jonny', 18 from dual
where not exists(select 1 from table_name where name = 'jonny');

Answer 8

You should use Merge: For example:

MERGE INTO employees e
    USING (SELECT * FROM hr_records WHERE start_date > ADD_MONTHS(SYSDATE, -1)) h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)
    VALUES (h.emp_id, h.address);

or

MERGE INTO employees e
    USING hr_records h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)
    VALUES (h.emp_id, h.address);

https://oracle-base.com/articles/9i/merge-statement

Answer 9

CTE and only CTE :-)

just throw out extra stuff. Here is almost complete and verbose form for all cases of life. And you can use any concise form.

INSERT INTO reports r
  (r.id, r.name, r.key, r.param)

--

  -- Invoke this script from "WITH" to the end (";")
  -- to debug and see prepared values.
  WITH

  -- Some new data to add.
  newData AS(
          SELECT 'Name 1' name, 'key_new_1' key FROM DUAL
    UNION SELECT 'Name 2' NAME, 'key_new_2' key FROM DUAL
    UNION SELECT 'Name 3' NAME, 'key_new_3' key FROM DUAL
    ),
  -- Any single row for copying with each new row from "newData",
  -- if you will of course.
  copyData AS(
      SELECT r.*
      FROM reports r
      WHERE r.key = 'key_existing'
        -- ! Prevent more than one row to return.
        AND FALSE -- do something here for than!
    ),
  -- Last used ID from the "reports" table (it depends on your case).
  -- (not going to work with concurrent transactions)
  maxId AS (SELECT MAX(id) AS id FROM reports),

--

  -- Some construction of all data for insertion.
  SELECT maxId.id + ROWNUM, newData.name, newData.key, copyData.param
  FROM copyData
    -- matrix multiplication :)
    -- (or a recursion if you're imperative coder)
    CROSS JOIN newData
    CROSS JOIN maxId

--

  -- Let's prevent re-insertion.
  WHERE NOT EXISTS (
      SELECT 1 FROM reports rs
      WHERE rs.name IN(
        SELECT name FROM newData
      ));

I call it "IF NOT EXISTS" on steroids. So, this helps me and I mostly do so.