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

Hmm, the lines are spaced so close in my browser that some of the underscores are not showing. — Jacob
– Jacob, Commented Mar 19, 2010 at 17:58

Ian Boyd · Accepted Answer · 2016-05-13 17:09:44Z

114

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

edited May 13, 2016 at 17:09

answered Jun 26, 2012 at 21:48

Ian Boyd

259k272 gold badges920 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

avl_sweden Over a year ago

Won't the "UPDATE member_topic SET mt_notes = 'test'" line in the non-merge example update all rows in the table? Not what was intended.

Aaron Bertrand Over a year ago

I've found the IF EXISTS / UPDATE / ELSE / INSERT pattern to be the source of deadlocks, and incur additional I/O in the best of cases (particularly if the EXISTS / UPDATE has to perform a scan). A better pattern (again in my experience) is to just try and update, and insert if no rows were affected. UPDATE / IF @@ROWCOUNT = 0 / INSERT.

Ian Boyd Over a year ago

The IF EXISTS would only hold a lock (and cause a deadlock) if you were running in REPEATABLE READ or SERIALIZABLE (i.e. WITH (HOLDLOCK)) isolation level. Without it: MERGE and Upsert both have a race condition. With it: MERGE and Upset both have a deadlock chance.

Aaron Bertrand Over a year ago

Yes, I wrote that first article. My point was that they both need HOLDLOCK to avoid race conditions (surprised your answer doesn't have a HOLDLOCK) and that when you use UPSERT, you should just to UPDATE/IF @@ROWCOUNT/INSERT instead of what people typically do IF EXISTS / UPDATE / ELSE / INSERT. The latter has a greater chance of deadlocks.

Aaron Bertrand Over a year ago

Anyway my main reason for commenting is that I continue to recommend to people to stay the heck away from MERGE and use the UPDATE/IF @@ROWCOUNT = 0/INSERT pattern, using whatever locking mechanism is more appropriate for their environment.

|

Andrew · Accepted Answer · 2014-01-29 13:51:48Z

49

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.

edited Jan 29, 2014 at 13:51

Andrew

2,7453 gold badges23 silver badges34 bronze badges

answered Mar 19, 2010 at 18:02

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

5 Comments

Jacob Over a year ago

I don't have a someOtherTable. Are you saying I have to simulate one with something like this? USING (SELECT 0 mt_member, 110 mt_topic) as source

marc_s Over a year ago

@Jacob: you have to have a source, yes - a subquery like you mention should be fine - try it!

Nate Cook Over a year ago

For anyone confused by this answer, check out this other question.

marc_s Over a year ago

@NateCook: what about my answer is confusing? I'd like to improve it - if possible

Nate Cook Over a year ago

The confusion has to with a misunderstanding of the fundamentals on the part of the readers (myself included). There is nothing wrong with your answer. The other question just expands on the topic for further clarification.

Collectives™ on Stack Overflow

syntax for single row MERGE / upsert in SQL Server

2 Answers 2

6 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related