SQL Server 是否提供像 MySQL 的 ON DUPLICATE KEY UPDATE 这样的东西
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1197733/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does SQL Server Offer Anything Like MySQL's ON DUPLICATE KEY UPDATE
提问by Ben Griswold
In MySQL, if you specify ON DUPLICATE KEY UPDATEand 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:
在 MySQL 中，如果您指定ON DUPLICATE KEY UPDATE并且插入的行会导致 UNIQUE 索引或 PRIMARY KEY 中的重复值，则会执行旧行的 UPDATE。例如，如果列 a 声明为 UNIQUE 并包含值 1，则以下两个语句具有相同的效果：
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?
我不相信我在 T-SQL 中遇到过类似的事情。SQL Server 是否提供可与 MySQL 的 ON DUPLICATE KEY UPDATE 相媲美的东西？
采纳答案by michael pearson
There's no DUPLICATE KEY UPDATE equivalent, but MERGE and WHEN MATCHED might work for you
没有 DUPLICATE KEY UPDATE 等价物，但 MERGE 和 WHEN MATCHED 可能对你有用
回答by IvanD
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');
回答by shahkalpesh
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
SQL Server 2008 具有此功能，作为 TSQL 的一部分。
在此处查看有关 MERGE 声明的文档 - http://msdn.microsoft.com/en-us/library/bb510625.aspx
回答by Tetraneutron
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.
SQL Server 2000 以后有一个替代触发器的概念，它可以完成想要的功能——尽管在幕后隐藏着一个讨厌的触发器。
Check the section "Insert or update?"
检查“插入或更新？”部分。
http://msdn.microsoft.com/en-us/library/aa224818(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/aa224818(SQL.80).aspx
回答by mesutuk
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')
