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

User · Answer

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    oracle insert if row not exists insert if not exists oracle

User · Answer

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   oracletutorial com oracle-base com

User · Answer

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

User · Answer

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

User · Answer

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

[oracle] Oracle insert if not exists statement

Examples related to oracle