SQLDeveloper: Insert if not exists

Question

I have found several posts on how to do an insert if the record doesn't exist but I don't know why I can't get it to work. I always get an error message.

In SQLDeveloper, I just want to run the following query:

INSERT INTO TABLE_A VALUES(1, 'userX', 'x', 'y', 'z')

If there are no record for userX already, that is if the following select statement doesn't return anything:

SELECT * FROM TABLE_A where user = 'userX'

Thank you

Answer 1

One option is to use MERGE. Here's an example. Sample table first:

SQL> create table table_a
  2    (id       number,
  3     username varchar2(10),
  4     col      varchar2(1)
  5    );

Table created.

Let's see how it works:

SQL> merge into table_a a
  2    using (select 1       id,
  3                  'userX' username,
  4                  'x'     col
  5           from dual
  6          ) x
  7    on (a.id = x.id)
  8  when not matched then insert (id, username, col)
  9    values (x.id, x.username, x.col);

1 row merged.

SQL> select * From table_a;

        ID USERNAME   C
---------- ---------- -
         1 userX      x

OK, userX has been inserted. What happens if I try to insert it once again?

SQL> merge into table_a a
  2    using (select 1       id,
  3                  'userX' username,
  4                  'x'     col
  5           from dual
  6          ) x
  7    on (a.id = x.id)
  8  when not matched then insert (id, username, col)
  9    values (x.id, x.username, x.col);

0 rows merged.                                      --> nothing happened

SQL> select * from table_a;

        ID USERNAME   C
---------- ---------- -
         1 userX      x

Nothing happened; 0 rows merged.

What happens if I try with userY?

SQL> merge into table_a a
  2    using (select 2       id,
  3                  'userY' username,                  --> userY is here
  4                  'y'     col
  5           from dual
  6          ) x
  7    on (a.id = x.id)
  8  when not matched then insert (id, username, col)
  9    values (x.id, x.username, x.col);

1 row merged.                                           --> 1 row merged

SQL> select * from table_a;

        ID USERNAME   C
---------- ---------- -
         1 userX      x
         2 userY      y

SQL>

The result shows that both userX and userY are now in the table.