Does SQL Server Offer Anything Like MySQL's ON DUPLICATE KEY UPDATE
Solution 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
Solution 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');
Solution 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')
Solution 4
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
Solution 5
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
Ben Griswold
Principal consultant at Fairway Technologies in La Jolla, CA. Focused primarily on web development using the Microsoft stack. Community speaker, author of johnnycoder.com, writer of Herding Code show notes, open source contributor and author (most recently SharpRepository), and facilitator of multiple developer code clubs.
Updated on January 07, 2020Comments
-
Ben Griswold over 4 years
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?
-
Yinda Yin almost 15 yearsYou can simulate it with a stored procedure. I don't have the exact code off the top of my head.
-
Ben Griswold almost 15 years@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?
-
-
ErikE over 13 yearsJust 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 over 13 yearsJust 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.
-
Tschallacka almost 9 yearsYou sir... are a life saver. I really hate the microsoft documentations... they always give all examples except the ones that are used most frequently
-
Tschallacka almost 9 years
'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 theUSING
select or would just theid
be sufficient there? -
Jonathan Ramos about 7 yearsThanks 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 about 7 years
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. -
Don Sam about 4 yearsI 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 - stackoverflow.com/questions/58866622/…