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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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 gt  create table foo     2    name varchar2 10  primary key    3    age  number   4      Table created   SQL gt  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 gt     1 row merged   SQL gt     0 rows merged   SQL gt  select   from foo   NAME              AGE ---------- ---------- johnny

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You should use Merge  For example   MERGE INTO employees e     USING  SELECT   FROM hr records WHERE start date  gt  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

User · Answer

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

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

Examples related to oracle

Examples related to plsql