views:

1907

answers:

3

In MySQL, if you specify ON DUPLICATE KEY UPDATE and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

I don't believe I've come across anything of the like in T-SQL. Does SQL Server offer anything comparable to MySQL's ON DUPLICATE KEY UPDATE?

+1  A: 

There's no DUPLICATE KEY UPDATE equivalent, but MERGE and WHEN MATCHED might work for you

Inserting, Updating, and Deleting Data by Using MERGE

david bowies labyrinth crotch
Just be aware that MERGE is not immune to high-concurrency insert collision. You **must** use WITH (UPDLOCK, HOLDLOCK) for merge to not collide. Some ultra-high-transaction-per-second systems use a different strategy where the locks are not used, but any errors are trapped and then converted to an update.
Emtucifor
+1  A: 

SQL Server 2008 has this feature, as part of TSQL.
See documentation on MERGE statement here - http://msdn.microsoft.com/en-us/library/bb510625.aspx

shahkalpesh
Just be aware that MERGE is not immune to high-concurrency insert collision. You must use WITH (UPDLOCK, HOLDLOCK) for merge to not collide. Some ultra-high-transaction-per-second systems use a different strategy where the locks are not used, but any errors are trapped and then converted to an update.
Emtucifor
+1  A: 

SQL server 2000 onwards has a concept of instead of triggers, which can accomplish the wanted functionality - although there will be a nasty trigger hiding behind the scenes.

Check the section "Insert or update?"

http://msdn.microsoft.com/en-us/library/aa224818(SQL.80).aspx

Tetraneutron