Oracle SQL insert if not exist

Question

I have table foo with PK int id,varchar state

table foo:
- int id
- varchar state
- int code1
- int code2

I want to do an sql insert if the record not already exist.

To complete the insert statement, I have to retrieve some info with an insert select.

In input I have:

id = 1, state = 'A'

In the insert I have to fill the other field with values retrieved with insert select.

code1 and code2 where id = 1 (they are always the same for id = x)

Normally I have to do:

1- a select with where id=1 and state=1, and if not exist I will have to do the sql insert

2- if record not exist retrieve the values of fields code1 and code2 when id=1

3- do the insert

Is it possible to do all in one query?

select * from foo where id=1 and state = 'A'

if not exist already:

select code1,code2 from foo where id=1; #1,2
insert into foo(id, state, code1, code2) values (1,'A', 1, 2);

is it possible to merge in one query?

Thanks

Answer 1

You can use insert . . . select:

insert into foo (id, some)
    select 1, 'text'
    from dual
    where not exists (select 1 from foo where id = 1);

Answer 2

You may use the *magical Oracle hint IGNORE_ROW_ON_DUPKEY_INDEX for this purpose. The hint silently skips the duplicated inserted row - normally causing `ORA-00001: unique constraint (...) violated.

I assume following table setup:

create table foo
(id number not null,
 status varchar2(1) not NULL,
 code1 varchar2(10),
 code2 varchar2(10));

alter table foo add (primary key (id, status)); 

insert into foo (id,status, code1,code2) values(1,'a','xxx', 'xxx');
insert into foo (id,status, code1,code2) values(1,'b','xxx', 'xxx');

insert into foo (id,status, code1,code2) values(2,'b','yyy', 'yyy');
commit;

So now you want to ignore the insert with key 1 and perform the insert with key 2 using the codes from the record with status 'b'

This should give you the idea how to proceed:

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX (foo (id, status)) */ into foo (id,status, code1,code2) 
values( 1, 'a', 
(select code1 from foo where id = 1 and status = 'b'),
(select code2 from foo where id = 1 and status = 'b'));

0 rows created.

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX (foo (id, status)) */ into foo (id,status, code1,code2) 
values( 2, 'a', 
(select code1 from foo where id = 2 and status = 'b'),
(select code2 from foo where id = 2 and status = 'b'));

1 rows created.

Adjust the subquery to get the code1 and code2 as required - you may use max(code1) and ignore the status.

Use bind variable to pass the id (three times) in the query.

Answer 3

In your table, id is a primary key. So oracle will throw an error if you're trying to insert a row with an existing id. You can handle that error however you wish.

But if you "really" want to check before inserting, you can try a PL/SQL block.

PL/SQL

declare
  _id INTEGER
begin
  SELECT id into _id from foo;
exception
  when NO_DATA_FOUND then
    insert into foo(id,some) values (1,'text');
end;
/

NOTE: Make sure you have the / at the end. This will run your PL/SQL block.

Basically this block tries to select a row with the given id. If exists, simply ends the block normally. If no rows are returned, then this is an 'exception' for the block and you 'handle' it by inserting what you need to.

Another way

begin
  insert into foo(id,some) values (1,'text');
exception
  when DUP_VAL_ON_INDEX then
    DBMS_OUTPUT.PUT_LINE('Already exists');
end;
/

NOTE: If you want to see any output of a PL/SQL block, you may need to do set serveroutput on; before running any PL/SQL block.

Hope this helps.

Answer 4

The way to do this in a single statement in Oracle is to use the MERGE statement. In your case you'd do something like:

MERGE INTO FOO
  USING (SELECT f.ID,
                'A' AS STATE,
                f.CODE1,
                f.CODE2
           FROM FOO f
           WHERE f.ID = 1) d
    ON (d.ID = FOO.ID AND
        d.STATE = FOO.STATE)
  WHEN NOT MATCHED THEN INSERT
    (ID, STATE, CODE1, CODE2)
  VALUES
    (d.ID, d.STATE, d.CODE1, d.CODE2)

Best of luck.