Does SQL Server Offer Anything Like MySQL's ON DUPLICATE KEY UPDATE

Question

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?

Answer 1

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

Inserting, Updating, and Deleting Data by Using MERGE

Answer 2

I was surprised that none of the answers on this page contained an example of an actual query, so here you go:

A more complex example of inserting data and then handling duplicate

MERGE INTO MyBigDB.dbo.METER_DATA WITH (HOLDLOCK) AS target USING (SELECT     77748 AS rtu_id    ,'12B096876' AS meter_id    ,56112 AS meter_reading    ,'20150602 00:20:11' AS local_time) AS source (rtu_id, meter_id, meter_reading, time_local) ON (target.rtu_id = source.rtu_id   AND target.time_local = source.time_local) WHEN MATCHED   THEN UPDATE       SET meter_id = '12B096876'          ,meter_reading = 56112 WHEN NOT MATCHED   THEN INSERT (rtu_id, meter_id, meter_reading, time_local)       VALUES (77748, '12B096876', 56112, '20150602 00:20:11'); 

Answer 3

You can try the other way around. It does the same thing more or less.

UPDATE tablename 
SET    field1 = 'Test1', 
       field2 = 'Test2' 
WHERE  id = 1 

IF @@ROWCOUNT = 0 
  INSERT INTO tablename 
              (id, 
               field1, 
               field2) 
  VALUES      (1, 
               'Test1', 
               'Test2')