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, Mar 19 '10 at 17:58

Ian Boyd · Answer 1 · 2016-05-13T17:09:44.477

110

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 '16 at 17:09

answered Jun 26 '12 at 21:48

Ian Boyd

246,734
253
869
1,219

8

In the `WHEN NOT MATCHED THEN` clause, why do you insert `VALUES (mt_member, mt_topic, mt_notes)` and not `VALUES (foo.mt_member, foo.mt_topic, foo.mt_notes)`? – Sam P Oct 18 '13 at 16:18
@SamP Because why not? Is it like SQL Server is going to get the values from someplace *other* than the pseudo-table `foo`? Oh wait... – Ian Boyd Jan 31 '16 at 17:17
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. – avl_sweden May 13 '16 at 13:37
3

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`. – Aaron Bertrand Jan 20 '17 at 01:57
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](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/). With it: [MERGE and Upset both have a deadlock chance.](http://dba.stackexchange.com/questions/23467/merge-statement-deadlocking-itself) – Ian Boyd Jan 20 '17 at 16:01
1

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 Jan 26 '17 at 20:42
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. – Aaron Bertrand Jan 26 '17 at 20:44
([This one's worth a read too](http://michaeljswart.com/2012/08/be-careful-with-the-merge-statement/).) – Aaron Bertrand Jan 26 '17 at 21:00

score 47 · Accepted Answer · edited Jan 29 '14 at 13:51

47

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 '14 at 13:51

Andrew

2,605
3
23
34

answered Mar 19 '10 at 18:02

marc_s

732,580
175
1,330
1,459

9

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 – Jacob Mar 19 '10 at 18:17
1

@Jacob: you have to have a source, yes - a subquery like you mention should be fine - try it! – marc_s Mar 19 '10 at 19:59
5

For anyone confused by this answer, check out [this other question](http://stackoverflow.com/questions/11216067/what-is-using-in-sql-server-2008-merge-syntax). – Nate Cook Nov 26 '12 at 19:13
1

@NateCook: what about my answer is confusing? I'd like to improve it - if possible – marc_s Nov 26 '12 at 19:48
1

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. – Nate Cook Dec 13 '12 at 09:17

syntax for single row MERGE / upsert in SQL Server

2 Answers2

Linked