syntax for single row MERGE / upsert in SQL Server(SQL Server 中单行 MERGE/upsert 的语法)
问题描述
我正在尝试对表进行单行插入/更新,但所有示例都是针对集合的.
I'm trying to do a single row insert/update on a table but all the examples out there are for sets.
谁能修复我的语法:
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 命令并不是真正用于单行 upserts.
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 的语法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:SQL Server 中单行 MERGE/upsert 的语法
基础教程推荐
- Sql Server 字符串到日期的转换 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01