Oracle how to UPSERT update or insert into a table

Question

The UPSERT operation either updates or inserts a row in a table  depending if the table already has a row that matches the data   if table t has a row exists that has key X      update t set mystuff    where mykey X else     insert into t mystuff      Since Oracle doesn t have a specific UPSERT statement  what s the best way to do this

User · Accepted Answer

An alternative to MERGE  the  old fashioned way     begin    insert into t  mykey  mystuff         values   X   123   exception    when dup val on index then       update t        set    mystuff   123        where  mykey    X   end

User · Answer

Another alternative without the exception check   UPDATE tablename     SET val1   in val1          val2   in val2     WHERE val3   in val3   IF   sql rowcount   0       THEN     INSERT INTO tablename         VALUES  in val1  in val2  in val3   END IF

User · Answer

A note regarding the two solutions that suggest   1  Insert  if exception then update   or  2  Update  if sql rowcount   0 then insert  The question of whether to insert or update first is also application dependent   Are you expecting more inserts or more updates   The one that is most likely to succeed should go first     If you pick the wrong one you will get a bunch of unnecessary index reads   Not a huge deal but still something to consider

User · Answer

Try this   insert into b building property     select      AREA IN COMMON USE DOUBLE   Area in Common Use   DOUBLE   null  9000  9   from dual   minus     select   from b building property where id   9

User · Answer

Another alternative without the exception check   UPDATE tablename     SET val1   in val1          val2   in val2     WHERE val3   in val3   IF   sql rowcount   0       THEN     INSERT INTO tablename         VALUES  in val1  in val2  in val3   END IF

User · Answer

The dual example above which is in PL SQL was great becuase I wanted to do something similar  but I wanted it client side   so here is the SQL I used to send a similar statement direct from some C   MERGE INTO Employee USING dual ON    id  2097153   WHEN MATCHED THEN UPDATE SET  last   smith     name   john  WHEN NOT MATCHED THEN INSERT   id   last   name        VALUES   2097153  smith    john      However from a C  perspective this provide to be slower than doing the update and seeing if the rows affected was 0 and doing the insert if it was

User · Answer

Try this   insert into b building property     select      AREA IN COMMON USE DOUBLE   Area in Common Use   DOUBLE   null  9000  9   from dual   minus     select   from b building property where id   9

User · Answer

None of the answers given so far is safe in the face of concurrent accesses  as pointed out in Tim Sylvester s comment  and will raise exceptions in case of races  To fix that  the insert update combo must be wrapped in some kind of loop statement  so that in case of an exception the whole thing is retried   As an example  here s how Grommit s code can be wrapped in a loop to make it safe when run concurrently   PROCEDURE MyProc          IS BEGIN  LOOP   BEGIN     MERGE INTO Employee USING dual ON    id  2097153         WHEN MATCHED THEN UPDATE SET  last   smith     name   john        WHEN NOT MATCHED THEN INSERT   id   last   name            VALUES   2097153  smith    john         EXIT  -- success  - gt  exit loop   EXCEPTION     WHEN NO DATA FOUND THEN -- the entry was concurrently deleted       NULL  -- exception  - gt  no op  i e  continue looping     WHEN DUP VAL ON INDEX THEN -- an entry was concurrently inserted       NULL  -- exception  - gt  no op  i e  continue looping   END   END LOOP  END     N B  In transaction mode SERIALIZABLE  which I don t recommend btw  you might run into ORA-08177  can t serialize access for this transaction exceptions instead

User · Answer

The MERGE statement  merges data between two tables   Using DUAL allows us to use this command   Note that this is not protected against concurrent access   create or replace procedure ups xa number  as begin     merge into mergetest m using dual on  a   xa           when not matched then insert  a b  values  xa 1               when matched then update set b   b 1  end ups    drop table mergetest  create table mergetest a number  b number   call ups 10   call ups 10   call ups 20   select   from mergetest   A                      B ---------------------- ---------------------- 10                     2 20                     1

User · Answer

The MERGE statement  merges data between two tables   Using DUAL allows us to use this command   Note that this is not protected against concurrent access   create or replace procedure ups xa number  as begin     merge into mergetest m using dual on  a   xa           when not matched then insert  a b  values  xa 1               when matched then update set b   b 1  end ups    drop table mergetest  create table mergetest a number  b number   call ups 10   call ups 10   call ups 20   select   from mergetest   A                      B ---------------------- ---------------------- 10                     2 20                     1

User · Answer

Copy  amp  paste example for upserting one table into another  with MERGE   CREATE GLOBAL TEMPORARY TABLE t1      id VARCHAR2 5         value VARCHAR2 5        value2 VARCHAR2 5           ON COMMIT DELETE ROWS   CREATE GLOBAL TEMPORARY TABLE t2      id VARCHAR2 5         value VARCHAR2 5        value2 VARCHAR2 5     ON COMMIT DELETE ROWS  ALTER TABLE t2 ADD CONSTRAINT PK LKP MIGRATION INFO PRIMARY KEY  id    insert into t1 values   a   1   1    insert into t1 values   b   4   5    insert into t2 values   b   2   2    insert into t2 values   c   3   3      merge into t2 using t1 on  t1 id   t2 id   when matched then    update set t2 value   t1 value    t2 value2   t1 value2 when not matched then   insert  t2 id  t2 value  t2 value2      values t1 id  t1 value  t1 value2    select   from t2   Result    b   4   5   c   3   3   a   1   1

User · Answer

I d like Grommit answer  except it require dupe values  I found solution where it may appear once  http   forums devshed com showpost php p 1182653 amp postcount 2  MERGE INTO KBS NUFUS MUHTARLIK B USING       SELECT  028-01  CILT   25  SAYFA   6  KUTUK   46603404838  MERNIS NO     FROM DUAL   E ON  B MERNIS NO   E MERNIS NO  WHEN MATCHED THEN     UPDATE SET B CILT   E CILT  B SAYFA   E SAYFA  B KUTUK   E KUTUK WHEN NOT MATCHED THEN     INSERT    CILT    SAYFA    KUTUK    MERNIS NO      VALUES  E CILT  E SAYFA  E KUTUK  E MERNIS NO

User · Answer

None of the answers given so far is safe in the face of concurrent accesses  as pointed out in Tim Sylvester s comment  and will raise exceptions in case of races  To fix that  the insert update combo must be wrapped in some kind of loop statement  so that in case of an exception the whole thing is retried   As an example  here s how Grommit s code can be wrapped in a loop to make it safe when run concurrently   PROCEDURE MyProc          IS BEGIN  LOOP   BEGIN     MERGE INTO Employee USING dual ON    id  2097153         WHEN MATCHED THEN UPDATE SET  last   smith     name   john        WHEN NOT MATCHED THEN INSERT   id   last   name            VALUES   2097153  smith    john         EXIT  -- success  - gt  exit loop   EXCEPTION     WHEN NO DATA FOUND THEN -- the entry was concurrently deleted       NULL  -- exception  - gt  no op  i e  continue looping     WHEN DUP VAL ON INDEX THEN -- an entry was concurrently inserted       NULL  -- exception  - gt  no op  i e  continue looping   END   END LOOP  END     N B  In transaction mode SERIALIZABLE  which I don t recommend btw  you might run into ORA-08177  can t serialize access for this transaction exceptions instead

User · Answer

Copy  amp  paste example for upserting one table into another  with MERGE   CREATE GLOBAL TEMPORARY TABLE t1      id VARCHAR2 5         value VARCHAR2 5        value2 VARCHAR2 5           ON COMMIT DELETE ROWS   CREATE GLOBAL TEMPORARY TABLE t2      id VARCHAR2 5         value VARCHAR2 5        value2 VARCHAR2 5     ON COMMIT DELETE ROWS  ALTER TABLE t2 ADD CONSTRAINT PK LKP MIGRATION INFO PRIMARY KEY  id    insert into t1 values   a   1   1    insert into t1 values   b   4   5    insert into t2 values   b   2   2    insert into t2 values   c   3   3      merge into t2 using t1 on  t1 id   t2 id   when matched then    update set t2 value   t1 value    t2 value2   t1 value2 when not matched then   insert  t2 id  t2 value  t2 value2      values t1 id  t1 value  t1 value2    select   from t2   Result    b   4   5   c   3   3   a   1   1

User · Answer

Another alternative without the exception check   UPDATE tablename     SET val1   in val1          val2   in val2     WHERE val3   in val3   IF   sql rowcount   0       THEN     INSERT INTO tablename         VALUES  in val1  in val2  in val3   END IF

User · Answer

insert if not exists  update         INSERT INTO mytable  id1  t1     SELECT 11   x1  FROM DUAL    WHERE NOT EXISTS  SELECT id1 FROM mytble WHERE id1   11     UPDATE mytable SET t1    x1  WHERE id1   11

User · Answer

From http   www praetoriate com oracle tips upserts htm    In Oracle9i  an UPSERT can accomplish this task in a single statement    INSERT FIRST WHEN    credit limit  gt  100000 THEN INTO    rich customers VALUES cust id cust credit limit     INTO customers ELSE    INTO customers SELECT   FROM new customers

User · Answer

The dual example above which is in PL SQL was great becuase I wanted to do something similar  but I wanted it client side   so here is the SQL I used to send a similar statement direct from some C   MERGE INTO Employee USING dual ON    id  2097153   WHEN MATCHED THEN UPDATE SET  last   smith     name   john  WHEN NOT MATCHED THEN INSERT   id   last   name        VALUES   2097153  smith    john      However from a C  perspective this provide to be slower than doing the update and seeing if the rows affected was 0 and doing the insert if it was

User · Answer

I d like Grommit answer  except it require dupe values  I found solution where it may appear once  http   forums devshed com showpost php p 1182653 amp postcount 2  MERGE INTO KBS NUFUS MUHTARLIK B USING       SELECT  028-01  CILT   25  SAYFA   6  KUTUK   46603404838  MERNIS NO     FROM DUAL   E ON  B MERNIS NO   E MERNIS NO  WHEN MATCHED THEN     UPDATE SET B CILT   E CILT  B SAYFA   E SAYFA  B KUTUK   E KUTUK WHEN NOT MATCHED THEN     INSERT    CILT    SAYFA    KUTUK    MERNIS NO      VALUES  E CILT  E SAYFA  E KUTUK  E MERNIS NO

User · Answer

insert if not exists  update         INSERT INTO mytable  id1  t1     SELECT 11   x1  FROM DUAL    WHERE NOT EXISTS  SELECT id1 FROM mytble WHERE id1   11     UPDATE mytable SET t1    x1  WHERE id1   11

User · Answer

I ve been using the first code sample for years  Notice notfound rather than count   UPDATE tablename SET val1   in val1  val2   in val2     WHERE val3   in val3  IF   sql notfound   THEN     INSERT INTO tablename         VALUES  in val1  in val2  in val3   END IF    The code below is the possibly new and improved code  MERGE INTO tablename USING dual ON   val3   in val3   WHEN MATCHED THEN UPDATE SET val1   in val1  val2   in val2 WHEN NOT MATCHED THEN INSERT      VALUES  in val1  in val2  in val3    In the first example the update does an index lookup  It has to  in order to update the right row  Oracle opens an implicit cursor  and we use it to wrap a corresponding insert so we know that the insert will only happen when the key does not exist  But the insert is an independent command and it has to do a second lookup  I don t know the inner workings of the merge command but since the command is a single unit  Oracle could have execute the correct insert or update with a single index lookup   I think merge is better when you do have some processing to be done that means taking data from some tables and updating a table  possibly inserting or deleting rows  But for the single row case  you may consider the first case since the syntax is more common

User · Answer

I ve been using the first code sample for years  Notice notfound rather than count   UPDATE tablename SET val1   in val1  val2   in val2     WHERE val3   in val3  IF   sql notfound   THEN     INSERT INTO tablename         VALUES  in val1  in val2  in val3   END IF    The code below is the possibly new and improved code  MERGE INTO tablename USING dual ON   val3   in val3   WHEN MATCHED THEN UPDATE SET val1   in val1  val2   in val2 WHEN NOT MATCHED THEN INSERT      VALUES  in val1  in val2  in val3    In the first example the update does an index lookup  It has to  in order to update the right row  Oracle opens an implicit cursor  and we use it to wrap a corresponding insert so we know that the insert will only happen when the key does not exist  But the insert is an independent command and it has to do a second lookup  I don t know the inner workings of the merge command but since the command is a single unit  Oracle could have execute the correct insert or update with a single index lookup   I think merge is better when you do have some processing to be done that means taking data from some tables and updating a table  possibly inserting or deleting rows  But for the single row case  you may consider the first case since the syntax is more common

User · Answer

A note regarding the two solutions that suggest   1  Insert  if exception then update   or  2  Update  if sql rowcount   0 then insert  The question of whether to insert or update first is also application dependent   Are you expecting more inserts or more updates   The one that is most likely to succeed should go first     If you pick the wrong one you will get a bunch of unnecessary index reads   Not a huge deal but still something to consider

User · Answer

The MERGE statement  merges data between two tables   Using DUAL allows us to use this command   Note that this is not protected against concurrent access   create or replace procedure ups xa number  as begin     merge into mergetest m using dual on  a   xa           when not matched then insert  a b  values  xa 1               when matched then update set b   b 1  end ups    drop table mergetest  create table mergetest a number  b number   call ups 10   call ups 10   call ups 20   select   from mergetest   A                      B ---------------------- ---------------------- 10                     2 20                     1

User · Answer

The MERGE statement  merges data between two tables   Using DUAL allows us to use this command   Note that this is not protected against concurrent access   create or replace procedure ups xa number  as begin     merge into mergetest m using dual on  a   xa           when not matched then insert  a b  values  xa 1               when matched then update set b   b 1  end ups    drop table mergetest  create table mergetest a number  b number   call ups 10   call ups 10   call ups 20   select   from mergetest   A                      B ---------------------- ---------------------- 10                     2 20                     1

User · Answer

Another alternative without the exception check   UPDATE tablename     SET val1   in val1          val2   in val2     WHERE val3   in val3   IF   sql rowcount   0       THEN     INSERT INTO tablename         VALUES  in val1  in val2  in val3   END IF

User · Answer

From http   www praetoriate com oracle tips upserts htm    In Oracle9i  an UPSERT can accomplish this task in a single statement    INSERT FIRST WHEN    credit limit  gt  100000 THEN INTO    rich customers VALUES cust id cust credit limit     INTO customers ELSE    INTO customers SELECT   FROM new customers

[sql] Oracle: how to UPSERT (update or insert into a table?)

Examples related to sql

Examples related to oracle

Examples related to merge

Examples related to upsert