我正在尝试在表上执行单行插入/更新，但所有示例都是针对集合的。
任何人都可以修复我的语法吗：
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 命令并不是真正用于单行更新插入。
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');
我终于使用了 Upsert 语法MERGE在 SQL Server 2008 中。使用什么 Jacobwanted要做的事（更新插入）：
IF EXISTS(SELECT * FROM member_topic WHERE mt_member = 0 AND mt_topic = 110)
BEGIN
--update existing row
UPDATE member_topic SET mt_notes = 'test'
WHERE mt_member = 0
AND mt_topic = 110
END
ELSE
BEGIN
--insert new row
INSERT INTO member_topic (mt_member, mt_topic, mt_notes)
VALUES (0, 110, 'test')
END
相当于MERGE语法是：
MERGE member_topic
USING (
VALUES (0, 110, 'test')
) AS foo (mt_member, mt_topic, mt_notes)
ON member_topic.mt_member = foo.mt_member
AND member_topic.mt_topic = foo.mt_topic
WHEN MATCHED THEN
UPDATE SET mt_notes = foo.mt_notes
WHEN NOT MATCHED THEN
INSERT (mt_member, mt_topic, mt_notes)
VALUES (foo.mt_member, foo.mt_topic, foo.mt_notes)
; --A MERGE statement must be terminated by a semi-colon (;).
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
