[SQL] – “IF EXISTS UPDATE ELSE INSERT” for MS Sql Server / MySql

This is a very common situation that comes up when performing database operations. The data needs to be updated if it already exists and inserted if it does not. I will show you how to do this with 2 most common DBMS nowadays: MS SQL Server and My SQL.

MS SQL Server

If we refer to the Books Online documentation, it gives examples that are similar to:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')

UPDATE Table1 SET (...) WHERE Column1='SomeValue'

ELSE

INSERT INTO Table1 VALUES (...)

This approach does work, however it might not always be the best approach. This will do a table/index scan for both the SELECT statement and the UPDATE statement. In most standard approaches, the following statement will likely provide better performance. It will only perform one table/index scan instead of the two that are performed in the previous approach.

UPDATE Table1 SET (...) WHERE Column1='SomeValue'

IF @@ROWCOUNT=0

INSERT INTO Table1 VALUES (...)

The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.

Just remember, the examples in the MSDN documentation are usually the easiest way to implement something, not necessarily the best way. Also (as I re-learned recently), with any database operation, it is good to performance test the different approaches that you take. Sometimes the method that you think would be the worst might actually outperform the way that you think would be the better way.

Reference: http://blogs.msdn.com/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx

My SQL

In My SQL, we use the INSERT… ON DUPLICATE KEY UPDATE… syntax.

Example:Table logs:

id: INT(11) auto_increment primary key

site_id: INT(11)

time: DATE

hits: INT(11)

Then:

CREATE UNIQUE INDEX comp ON logs (`site_id`, `time`);

And then you can:

INSERT INTO logs (`site_id`, `time`,`hits`) VALUES (1,"2004-08-09", 15) ON DUPLICATE KEY UPDATE hits = hits+15;

Excellent feature, and it is much faster and briefer then using first a select, then issuing either an update or an insert depending on the value of the select. You also get rid of the probably necessary table-lock during this action.

Reference: http://dev.mysql.com/doc/refman/5.0/en/insert.html