84

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?

Ben Griswold
  • 17,793
  • 14
  • 58
  • 60
  • You can simulate it with a stored procedure. I don't have the exact code off the top of my head. – Robert Harvey Jul 29 '09 at 02:13
  • @Robert Harvey - I've always performed with operation by checking if the row exists. If yes, then perform an update else insert a new row. Is that what you were thinking as well? – Ben Griswold Jul 29 '09 at 02:52

5 Answers5

66

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 time_local) 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');
IvanD
  • 7,971
  • 4
  • 35
  • 33
  • `'MERGE INTO MyFancyTableName WITH (HOLDLOCK) AS target USING (SELECT :id AS id, :lastaccess AS lastaccess ) AS source (id,lastaccess) ON (target.id = source.id) WHEN MATCHED THEN UPDATE SET lastaccess = :lastaccess2 WHEN NOT MATCHED THEN INSERT (id, lastaccess) VALUES (:id2, :lastaccess3);'` Do I need to post both variables in the `USING` select or would just the `id` be sufficient there? – Tschallacka Jul 01 '15 at 07:58
  • Thanks for the reference to Merge command. I should point that in the MATCHED STATEMENTS you are using the same values you have defined as source. So if your looking to do a update lot of records you should asigne the source value like this: – Jonathan Ramos Apr 12 '17 at 15:10
  • 1
    `WHEN MATCHED THEN UPDATE SET METER_ID = source.RTU_ID, METER_READING = source.METER_ID`. Sorry for the double comment, stackoverflow did not let me edit past 6 minutes. – Jonathan Ramos Apr 12 '17 at 15:19
  • I tried using MERGE INTO, but got an error that it could not be used on memory-optimized-tables. The tables that are created using CREATE TABLE statement are memory optimized tables by default. I don't know if using WITH (HOLDLOCK) would work. Will try it out. btw..the table in your code - MyBigDB.dbo.METER_DATA is it memory optimized? To find if a table is memory optimized, please refer to - https://stackoverflow.com/questions/58866622/how-to-determine-if-given-table-is-memory-optimized – Don Sam Jun 07 '20 at 22:34
54

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

Inserting, Updating, and Deleting Data by Using MERGE

Peter
  • 2,654
  • 2
  • 33
  • 44
michael pearson
  • 664
  • 6
  • 4
  • 15
    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. – ErikE Oct 02 '10 at 01:03
11

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')
4b0
  • 21,981
  • 30
  • 95
  • 142
mesutuk
  • 402
  • 3
  • 17
3

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
  • 33,172
  • 3
  • 63
  • 88
  • 1
    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. – ErikE Oct 02 '10 at 01:04
1

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

Jim
  • 22,354
  • 6
  • 52
  • 80
Tetraneutron
  • 32,841
  • 3
  • 25
  • 21