SQL Server中单行MERGE/upsert的语法 [英] syntax for single row MERGE / upsert in SQL Server

问题描述

我正在尝试对表进行单行插入/更新，但其中的所有示例都是针对集合的.

I'm trying to do a single row insert/update on a table but all the examples out there are for sets.

任何人都可以修复我的语法吗?

Can anyone fix my syntax please:

MERGE member_topic ON mt_member = 0 AND mt_topic = 110
WHEN MATCHED THEN UPDATE SET mt_notes = 'test'
WHEN NOT MATCHED THEN INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test')

每个marc_s的解决方案是将单行转换为子查询-这使我认为MERGE命令并不是真正针对单行upsert的.

Resolution per marc_s is to convert the single row to a subquery - which makes me think the MERGE command is not really intended for single row upserts.

MERGE member_topic
USING (SELECT 0 mt_member, 110 mt_topic) as source
ON member_topic.mt_member = source.mt_member AND member_topic.mt_topic = source.mt_topic
WHEN MATCHED THEN UPDATE SET mt_notes = 'test'
WHEN NOT MATCHED THEN INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test');

推荐答案

基本上，您处于正确的轨道上-但您缺少要合并数据的来源-请尝试以下操作:

Basically, you're on the right track - but you're missing a source from where you want to merge the data - try something like this:

MERGE 
   member_topic AS target
USING 
   someOtherTable AS source
ON 
   target.mt_member = source.mt_member 
   AND source.mt_member = 0 
   AND source.mt_topic = 110
WHEN MATCHED THEN 
   UPDATE SET mt_notes = 'test'
WHEN NOT MATCHED THEN 
   INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test')
; 

单行MERGE没有特殊的语法-您需要做的只是使用适当的子句.通过ON子句中的适当条件，您可以将源限制为单行-没问题.

There is no special syntax for a single row MERGE - all you need to do is use a proper clause. With that proper condition in the ON clause, you can limit the source to a single row - no problem.

不要忘了后面的分号！别开玩笑-这很重要！

And don't forget the trailing semicolon! No joke - it's important!

请参见此博客文章，非常好的MERGE简介.

See this blog post for a really good intro to MERGE.

这篇关于SQL Server中单行MERGE/upsert的语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！