Oracle SQL: Update If Exists Else Insert

ORACLE SQL : IF EXISTS UPDATE ELSE INSERT

MERGE is what we usually do. Here's an example:

Test table and sample data:

SQL> create table test (tn number, val1 number, val2 number);

Table created.

SQL> insert into test
  2    select 2250, 1000, 2000 from dual union all
  3    select 2251, 1000, 3000 from dual;

2 rows created.

SQL> select * From test order by tn;

        TN       VAL1       VAL2
---------- ---------- ----------
      2250       1000       2000
      2251       1000       3000

How to do it? using represents data you're going to insert or update:

SQL> merge into test t
  2    using (select 2250 tn, 1 val1, 2 val2 from dual union all  --> for update
  3           select 3000   , 8     , 9      from dual            --> for insert
  4          ) x
  5    on (t.tn = x.tn)
  6  when matched then update set t.val1 = x.val1,
  7                               t.val2 = x.val2
  8  when not matched then insert values (x.tn, x.val1, x.val2);

2 rows merged.

Result:

SQL> select * From test order by tn;

        TN       VAL1       VAL2
---------- ---------- ----------
      2250          1          2      --> updated
      2251       1000       3000
      3000          8          9      --> inserted

SQL>

UPDATE record if present; else INSERT in Oracle

You are looking for merge in Oracle.

MERGE
       INTO  target_table tgt
      USING source_table src
         ON  ( src.object_id = tgt.object_id ) //The key to check if the record exists
       WHEN MATCHED // if exists
       THEN
     UPDATE
        SET   tgt.object_name = src.object_name //update it
        ,     tgt.object_type = src.object_type
       WHEN NOT MATCHED                         // if not exists
       THEN
     INSERT ( tgt.object_id                    //then insert
            , tgt.object_name
            , tgt.object_type )
     VALUES ( src.object_id
            , src.object_name
            , src.object_type );

Update or insert based on if employee exist in table

This is quite a common pattern. Depending on what version of Oracle you are running, you could use the merge statement (I am not sure what version it appeared in).

create table test_merge (id integer, c2 varchar2(255));

create unique index test_merge_idx1 on test_merge(id);

merge into test_merge t
  using (select 1 id, 'foobar' c2 from dual) s
  on (t.id = s.id)
  when matched then update set c2 = s.c2
  when not matched then insert (id, c2) 
  values (s.id, s.c2);

Merge is intended to merge data from a source table, but you can fake it for individual rows by selecting the data from dual.

If you cannot use merge, then optimize for the most common case. Will the proc usually not find a record and need to insert it, or will it usually need to update an existing record?

If inserting will be most common, code such as the following is probably best:

begin
  insert into t (columns) 
  values ()
exception
  when dup_val_on_index then
    update t set cols = values
end;

If update is the most common, then turn the procedure around:

begin
  update t set cols = values;

  if sql%rowcount = 0 then  
    -- nothing was updated, so the record doesn't exist, insert it. 
    insert into t (columns)
            values ();
  end if;
end;

You should not issue a select to check for the row and make the decision based on the result - that means you will always need to run two SQL statements, when you can get away with one most of the time (or always if you use merge). The less SQL statements you use, the better your code will perform.

SQL: update if exists, else insert... but for multiple rows with different values

It doesn't have to be a table in the using clause, you can also use a SQL query.

At least that is what I think you are after :)

merge 
 into target_table
using (select case when ... then ... else ... end as id
         from dual
      ) source_table
    on(target_table.id = source_table.id)
when matched then
   update 
      set ...
when not matched then
   insert (...)
   values (...)

Let me know if that helps. If so, I can probably help write the final query as well.

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

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;   

Oracle SQL Developer: If Exists Update Else Insert Missing Right Parenthesis Compiler Error

In your case there is a little need of using any procedural processing to update active column of the table if records matching condition is true, or insert new record if it's not. You can do it in a single statement using merge.

merge 
 into unitofmeasure_user
using dual
  on (
      unitofmeasureid = :unitOfMeasureId AND 
      userid = :userId
     )
when matched 
then update 
        set active = 1
when not matched
then insert (unitofmeasureid, userid) 
       values (:unitOfMeasureId, :userId);

Insert into Oracle SQL or Update if value already exist?

Merge does not work with INSERT ALL. If you want to use merge, you should make from both table a view with instead of trigger and than use merge against the view. The whole logic will be inside the trigger.

EDIT: Merge does not work with such views
ORA-38106: MERGE bei Join View oder View mit INSTEAD OF-Trigger nicht unterstützt

You can make two merge statements (one for every table) or one for insert and one for update against view:

CREATE SEQUENCE MYDICT_SEQ START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 0;

CREATE VIEW mydict
AS
   SELECT a.id, a.origterm, a.code, b.replaceterm, b.sysctlang
     FROM table1 a LEFT OUTER JOIN table2 b ON a.id = b.id;   

CREATE OR REPLACE TRIGGER mydict_io
   INSTEAD OF INSERT OR UPDATE OR DELETE
   ON mydict
   FOR EACH ROW
DECLARE
   cnt1   INTEGER := 0;
   cnt2   INTEGER;
   nid    NUMBER;
BEGIN
   IF INSERTING OR UPDATING THEN
      IF :new.id IS NULL AND INSERTING THEN
         nid := mydict_seq.NEXTVAL;
      ELSE
         nid := :new.id;

         IF UPDATING THEN
            nid := :old.id;
         END IF;

         SELECT COUNT (*)
           INTO cnt1
           FROM table1
          WHERE id = nid;
      END IF;

      IF cnt1 = 0 THEN
         INSERT INTO TABLE1 (ID, ORIGTERM, CODE)
              VALUES (nID, :new.ORIGTERM, :new.CODE);
      ELSIF cnt1 > 0 THEN
         UPDATE TABLE1
            SET ORIGTERM = :NEW.ORIGTERM, CODE = :NEW.CODE
          WHERE id = nid;
      END IF;

      SELECT COUNT (*)
        INTO cnt2
        FROM table2
       WHERE id = nid AND SYSCTLANG = :new.SYSCTLANG;

      IF cnt2 = 0 THEN
         INSERT INTO TABLE2 (ID, REPLACETERM, SYSCTLANG)
              VALUES (nID, :new.REPLACETERM, :new.SYSCTLANG);
      ELSE
         UPDATE TABLE2
            SET REPLACETERM = :new.REPLACETERM
          WHERE id = nid AND SYSCTLANG = :new.SYSCTLANG;
      END IF;
   ELSIF DELETING THEN
      DELETE FROM table2
            WHERE id = :old.id AND SYSCTLANG = :old.SYSCTLANG;

      SELECT COUNT (*)
        INTO cnt2
        FROM table2
       WHERE id = nid;

      IF cnt2 = 0 THEN
         DELETE FROM table1
               WHERE id = :old.id;
      END IF;
   END IF;
END;

DECLARE
   nid   NUMBER;
BEGIN
   INSERT INTO mydict (ORIGTERM, CODE, REPLACETERM, SYSCTLANG)
        VALUES ('Plant', 'TEST', 'Pflanze', 3);
    nid := mydict_seq.currval;

   UPDATE mydict
      SET REPLACETERM = 'Fabrik'
    WHERE id = nid;

   UPDATE mydict
      SET REPLACETERM = 'Usine', SYSCTLANG = 4
    WHERE id = nid;
END;

The logic in the instead of trigger can be done better, but I leave this to you ;)

---

Related Topics