Syntax for single-line MERGE / upsert in SQL Server

Question

I am trying to insert / update a single row in a table, but all the examples are there for sets.

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

The resolution of per marc_s is to convert one line to a subquery - this makes me think that the MERGE command is not really intended for single-line 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

Basically, you are on the right track - but you are missing the source from which you want to combine 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 MERGE line - all you have to do is use the correct sentence. With this right condition, in the ON clause you can limit the source to one line - no problem.

And don't forget the end semicolon! No jokes - this is important!

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

Answer 2

Finally, I got the Upsert syntax using MERGE in SQL Server 2008. Using what Jacob wanted (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 

Equivalent MERGE syntax:

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