CODEWITHSUNDEEP
sqloracle-databaseinsert-updatesql-merge
Bora.Ozgur
Bora.Ozgur

Reputation: 65

ORACLE SQL : IF EXISTS UPDATE ELSE INSERT

Lets say :

i have data on OracleDb like what i mentioned above. 

TRANSFERNUMBER  |   VALUE1  | VALUE2 
2250            |   1000    |   2000
2251            |   1000    |   3000

My main purpose is when add some data on table if data exists it should update the data . if data not exists on the table it should insert new row on table . That is why i want to use if exists on my query . However i can't handle the query . Also i can't write procedure because of some reasons on the table . Is anyone help me for writing this by using query on Oracle ?

Upvotes: 0

Views: 1683

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 5

Related Questions