Oracle Insert or Update in single Query…

Oracle Insert or Update in single Query…

Often a situation comes in applications where you dont know whether a row was already present in DB or not, if it was present then update it otherwise insert the new row.

Suppose you will do a batch update and in this batch of data you have multiple items. If you want to update your DB with the latest data then you can do one of the followings:

•     Hit DB to find which data is not there and filter in your code to differentiate between which item should use 'Insert' and which items should  be 'Update'. But in this method the performance will be slower because you have to hit the DB twice for this and you will also filter data in your code that will cost you quite some time.  
• There are other solutions like you can use a stored proc to do the work etc.But m suggestion would be to use oracle "MERGE" statement.

Suppose you have a table called EMP_MASTER(id,name,phone).

It has ID as the primary key.

Now you got a bunch of employee records andd in one shot you want your table to be updated according to the new record.

Suppose earlier the table looked like this :

EMP_MASTER
---------------------------------------
id   -    name   -    phone
--------------------------------
001 - John - 3434353
002 - Mike - 312121

Now in the batch of records you have got (002,Mike,9898),(003,Lucy,2312).

Here number 002 was already present in the database. So if you do a blind update then the new record would throw an exception/or will be neglected based on your query.

So you can use the below MERGE statement:

MERGE INTO EMP_MASTER e USING dual ON (e.id=e.id and e.id=? )
WHEN MATCHED THEN UPDATE SET name=? , phone=?
WHEN NOT MATCHED THEN INSERT (id,name,phone)
    VALUES ( ?,?,? );

It is blazing fast even with a scale of 4000+ records.

I came across this problem a few months back and i had to google a lot to find a suitable solution. I use the above mentioned statement and it helps a lot.

Limitations:-
•    In the batch itself if you have two records with same primary key value and both were previously not present in DB then this statement might throw an exception when used with any framework's batch update statement. Because both the data with duplicate primary key would be tried to insert in single shot so it will throw an Integrity constraint violation exception.

•    You cannot use any key that is used in the 'ON' clause as part of the update statement.

Apart from these the statement is a sweeet for developers who face this problem.

Njoy \m/ :)