Oracle Insert If Row Does Not Exist

Insert if not exists Oracle

The statement is called MERGE. Look it up, I'm too lazy.

Beware, though, that MERGE is not atomic, which could cause the following effect (thanks, Marius):

SESS1:

create table t1 (pk int primary key, i int);
create table t11 (pk int primary key, i int);
insert into t1 values(1, 1);
insert into t11 values(2, 21);
insert into t11 values(3, 31);
commit;

SESS2: insert into t1 values(2, 2);

SESS1:

MERGE INTO t1 d
USING t11 s ON (d.pk = s.pk)
WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i);

SESS2: commit;

SESS1: ORA-00001

Oracle: how to INSERT if a row doesn't exist

INSERT INTO table
SELECT 'jonny', NULL
  FROM dual -- Not Oracle? No need for dual, drop that line
 WHERE NOT EXISTS (SELECT NULL -- canonical way, but you can select
                               -- anything as EXISTS only checks existence
                     FROM table
                    WHERE name = 'jonny'
                  )

Insert if not exists Oracle

The statement is called MERGE. Look it up, I'm too lazy.

Beware, though, that MERGE is not atomic, which could cause the following effect (thanks, Marius):

SESS1:

create table t1 (pk int primary key, i int);
create table t11 (pk int primary key, i int);
insert into t1 values(1, 1);
insert into t11 values(2, 21);
insert into t11 values(3, 31);
commit;

SESS2: insert into t1 values(2, 2);

SESS1:

MERGE INTO t1 d
USING t11 s ON (d.pk = s.pk)
WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i);

SESS2: commit;

SESS1: ORA-00001

Oracle insert if row does not exist

When I run this I get the error "missing INTO keyword" .

Because IGNORE is not a keyword in Oracle. That is MySQL syntax.

What you can do is use MERGE.

merge into table1 t1
    using (select 'value1' as value1 ,value2 
           from table2 
           where table2.type = 'ok' ) t2
    on ( t1.value1 = t2.value1)
when not matched then
   insert values (t2.value1, t2.value2)
/

From Oracle 10g we can use merge without handling both branches. In 9i we had to use a "dummy" MATCHED branch.

In more ancient versions the only options were either :

1. test for the row's existence before issuing an INSERT (or in a sub-query);
2. to use PL/SQL to execute the INSERT and handle any resultant DUP_VAL_ON_INDEX error.

Oracle: Insert if not exist or Update not working

Your original code does what it is meant to, that is: insert the row if the given (nis, semester) do not yet exist in the table.

However, your question mentions that you want to update as well, when a record already exists:

I am trying to insert if the record not exist otherwise update

In Oracle, you would use a merge statement for this. Based on your description of the problem, that would be:

merge into tb_coba2 t
using (select :nis nis, :nilai_a nilai_a, :semester semester from dual) s
on (s.nis = t.nis and s.semester = t.semester)
when matched then update set t.nilai_a = s.nilai_a
when not matched then insert (nis, nilai_a, semester) values (s.nis, s.nilai_a, s.semester)

INSERT INTO SELECT if NOT EXISTS in oracle

Use not exists:

INSERT INTO TABLE1 (VEH_YEAR, VEH_MAKE, ACV_VOLUME)
    SELECT VEH_YEAR, VEH_MAKE, 
           (SELECT COUNT(*)
            FROM ACV_VEHICLE_DETAILS vd
            WHERE vd.YEAR = t2.veh_year AND vd.MAKE = t2.veh_make
           ) AS ACV_VOLUME
    FROM TABLE2 t2 
    WHERE VEH_YEAR IS NOT NULL AND VEH_MAKE IS NOT NULL AND
          NOT EXISTS (SELECT 1
                      FROM table1 t1
                      WHERE t1.veh_year = t2.veh_year and t1.veh_make = t2.veh_make
                     );

sql - insert if not exists

Do it all in SQL rather than context switching into PL/SQL:

INSERT INTO DATA1.FOLDERS
(folder_id,
 user_id)
SELECT f1.folder_id,
       f1.user_id
  FROM DATA1.FOLDERS f1
 WHERE NOT EXISTS (SELECT 1
                     FROM DATA1.FOLDERS f2
                    WHERE f1.folder_id = f2.folder_id
                      AND f1.user_id = f2.user_id);

Oracle - how to insert if not exists?

Use an INSERT .. SELECT statement with a PARTITIONed outer join:

INSERT INTO stats_client_test (
  codeclient, codeaxestat, codeelementstat, valeuraxestatistiqueclient
)
  SELECT cc.codeclient,
         s.codeaxestat,
         s.codeelementstat,
         'UNKNOWN'
  FROM   (SELECT DISTINCT codeclient FROM stats_client_test) cc
         LEFT OUTER JOIN stats_client_test s
         PARTITION BY (s.codeaxestat, s.codeelementstat)
         ON (s.codeclient = cc.codeclient)
  WHERE  s.rowid IS NULL;

or a MERGE statement:

MERGE INTO stats_client_test dst
USING (
  SELECT cc.codeclient,
         s.codeaxestat,
         s.codeelementstat,
         s.ROWID AS rid
  FROM   (SELECT DISTINCT codeclient FROM stats_client_test) cc
         LEFT OUTER JOIN stats_client_test s
         PARTITION BY (s.codeaxestat, s.codeelementstat)
         ON (s.codeclient = cc.codeclient)
) src
ON (dst.ROWID = src.rid)
WHEN NOT MATCHED THEN
  INSERT (codeclient, codeaxestat, codeelementstat, valeuraxestatistiqueclient)
  VALUES (src.codeclient, src.codeaxestat, src.codeelementstat, 'UNKNOWN');

db<>fiddle here

---

Related Topics