syntax for single row MERGE / upsert in SQL Server

Question

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

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

Answer 1

i finally got the Upsert syntax using MERGE in SQL Server 2008. Using what Jacob wanted to do (an Upsert):

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 

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

Answer 2

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

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!

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