Question

我试图在表格上插入/更新一个单行,但此处列举的所有例子都有。

• 任何人都可以找到我的子,请:

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 )

每一军舰的解决是将单一行变成一个分局,因此,我认为市面汇率的指挥并非真正用于单一管束。

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 );

Answer 1

基本上,你回到正确的轨道上——但你再次从你想要合并数据的来源中找不到——试图这样做:

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 )
;

没有一个专门的子公司可以使用适当的条款。适当条件载于<代码> ON 条款,你可以将来源限制在单一一行——不成问题。

不要忘记拖 trail的半殖民地! No!

见,该博客后, 真正良好,可追溯到MERGE。

Answer 2

i 最后使用GEMER进入服务器2008年。利用Jacob wanted做(更新):

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 syntax is:

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 (;).

友情链接