Oracle insert if not exists statement

Question

insert into OPT (email, campaign_id) values('mom@cox.net',100)
where not exists( select * from OPT where (email ="mom@cox.net" and campaign_id =100)) ;

Error report: SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action:

how to insert a new row if it doesn't exists in Oracle?

score 110 · Answer 1 · edited Jul 23 '20 at 14:09

110

insert into OPT (email, campaign_id) 
select 'mom@cox.net',100
from dual
where not exists(select * 
                 from OPT 
                 where (email ='mom@cox.net' and campaign_id =100));

edited Jul 23 '20 at 14:09

Anjana Silva

8,353
4
51
54

answered May 19 '13 at 22:01

Simple and clever for bulk insert. Thanks – Farid Jan 16 '19 at 10:16

score 28 · Answer 2 · edited May 23 '17 at 11:46

28

The correct way to insert something (in Oracle) based on another record already existing is by using the MERGE statement.

Please note that this question has already been answered here on SO:

edited May 23 '17 at 11:46

Community

1
1

answered May 31 '12 at 08:17

p.marino

6,244
3
25
36

score 12 · Answer 3 · answered Oct 22 '18 at 07:12

12

MERGE INTO OPT
USING
    (SELECT 1 "one" FROM dual) 
ON
    (OPT.email= 'mom@cox.net' and OPT.campaign_id= 100) 
WHEN NOT matched THEN
INSERT (email, campaign_id)
VALUES ('mom@cox.net',100) 
;

answered Oct 22 '18 at 07:12

ayush baran

361
3
3

10

From Review: Hi, please don't answer just with source code. Try to provide a nice description about how your solution works. See: [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer). Thanks – sɐunıɔןɐqɐp Oct 22 '18 at 07:30

René Nyffenegger · Answer 4 · 2013-05-19T22:08:14.787

insert into OPT       (email,        campaign_id) 
select 'mom@coxnet' as email, 100 as campaign_id from dual MINUS
select                 email,        campaign_id from OPT;

If there is already a record with mom@cox.net/100 in OPT, the MINUS will subtract this record from the select 'mom@coxnet' as email, 100 as campaign_id from dual record and nothing will be inserted. On the other hand, if there is no such record, the MINUS does not subract anything and the values mom@coxnet/100 will be inserted.

As p.marino has already pointed out, merge is probably the better (and more correct) solution for your problem as it is specifically designed to solve your task.

score 1 · Answer 5 · answered Aug 07 '20 at 10:50

Another approach would be to leverage the INSERT ALL syntax from oracle,

INSERT ALL 
    INTO table1(email, campaign_id) VALUES (email, campaign_id)
WITH source_data AS
 (SELECT 'mom@cox.net' email,100 campaign_id
  FROM   dual
  UNION ALL
  SELECT 'dad@cox.com' email,200 campaign_id
  FROM   dual)      
SELECT email
      ,campaign_id
FROM   source_data src
WHERE  NOT EXISTS (SELECT 1
        FROM   table1 dest
        WHERE  src.email = dest.email
        AND    src.campaign_id = dest.campaign_id);

INSERT ALL also allow us to perform a conditional insert into multiple tables based on a sub query as source.

There are some really clean and nice examples are there to refer.

Oracle insert if not exists statement

5 Answers5

Linked

Related