UpdateOrInsert (Upsert)

Hibernate used an InsertOrUpdate (aka Upsert) method to save values (netTiers uses Save for the same thing).  This made things really simple, no need to work out if you have inserted the object, just call InsertOrUpdate and Bob’s your uncle.  Here I present the InsertOrUpdate method (well, copied from http://blogs.msdn.com/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx but I made it universally applicable 🙂 .  A handy little thing that lets you use do both.

(MS SQL)

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
   INSERT INTO Table1 VALUES (...)

It’s nice, it’s simple and does what needs to be done without a lot of work. 

Here is the same approach can be used for postgresql, oracle and mysql.

(PostgreSQL)

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF NOT FOUND
   INSERT INTO Table1 VALUES (...)

(Oracle)

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF sql%rowcount=0
   INSERT INTO Table1 VALUES (...)

(MySQL)

INSERT INTO Table1 (Column1, ...) VALUES (SomeValue, ...)
ON DUPLICATE KEY UPDATE ...

See also:

http://blog.cutterscrossing.com/index.cfm/2008/2/4/SQL-Tricks-Whats-an-Upsert

UPDATE: Btw, if you’re using SQL Server 2008 you can use MERGE to do this.

Comments (2)

  1. Dobes

    Couldn’t the same row be inserted in between the update and the insert when you use this approach? Unless you are running in SERIALIZED isolation level?

Leave a Reply