syntax for single row MERGE / upsert in SQL Server

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 = 110ENDELSEBEGIN    --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_topicUSING (     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_topicWHEN MATCHED THEN   UPDATE SET mt_notes = foo.mt_notesWHEN 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 (;).

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 targetUSING    someOtherTable AS sourceON    target.mt_member = source.mt_member    AND source.mt_member = 0    AND source.mt_topic = 110WHEN 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.