Oracle insert if not exists statement
OracleOracle Problem Overview
insert into OPT (email, campaign_id) values('[email protected]',100)
where not exists( select * from OPT where (email ="[email protected]" 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?
Oracle Solutions
Solution 1 - Oracle
insert into OPT (email, campaign_id)
select '[email protected]',100
from dual
where not exists(select *
from OPT
where (email ='[email protected]' and campaign_id =100));
Solution 2 - Oracle
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:
Solution 3 - Oracle
MERGE INTO OPT
USING
(SELECT 1 "one" FROM dual)
ON
(OPT.email= '[email protected]' and OPT.campaign_id= 100)
WHEN NOT matched THEN
INSERT (email, campaign_id)
VALUES ('[email protected]',100)
;
Solution 4 - Oracle
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 [email protected]
/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.
Solution 5 - Oracle
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 '[email protected]' email,100 campaign_id
FROM dual
UNION ALL
SELECT '[email protected]' 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.