using merge for updating

Question

I often run updates at work, however, I would like to start using the merge command. I am still not so familiar with it though.

How would you transalate, or at least give me an idea of the following update statement using MERGE?

BEGIN TRANSACTION T1

SELECT @@TRANCOUNT;

WITH RADHE2
     AS (SELECT ROW_NUMBER () OVER( PARTITION BY Assigned_To, Assigned_To_Descriptor /*,..*/
                                     /* Irrelevant columns removed for brevity*/
                                        ORDER BY Rn_Create_Date DESC, Assigned_To DESC ) AS ID,
                *
         FROM   PivCRM_Prod_Online_ED.dbo.Rn_Appointments
         WHERE  AN_ACTIVE = 1
                AND rn_edit_date > '12 nov 2013'
                AND ASSIGNED_TO = 0x000000000000074F
                AND notes LIKE 'TAM_Indigo Base_Oct 2013')
--======================================================
--SETTING Appt_Date TO TOMORROW
UPDATE PivCRM_Prod_Online_ED.dbo.Rn_Appointments
SET    Appt_Date = '14 NOV 2013'
FROM   PivCRM_Prod_Online_ED.dbo.Rn_Appointments R
       INNER JOIN RADHE2 R1
         ON R.RN_APPOINTMENTS_ID = R1.Rn_APPOINTMENTS_ID
WHERE  R1.ID = 1
--COMMIT TRANSACTION T1

As far as I know, there's no obvious always applicable advantage to using merge. It's more complicated to use than a regular update and has no performance benefit(?). So you should switch to it only when you want to create one logical operation for insert / update / delete, merging the differences between the sets - hence MERGE -, or niche cases like the need to OUTPUT values from joined tables during an INSERT operation. — Kahn, Nov 13 '13 at 13:14
Why do you want to use MERGE for an UPDATE, especially when you are not familiar with it? [Please read this article before jumping all over it](http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/)... — Aaron Bertrand, Nov 13 '13 at 13:27
thanks for the comments Martin, Kahn and Aaron, much appreciated. for one offs no need to complicate really. yes, there were cases I needed an OUTPUT I have used merge.regards. — Marcello Miorelli, Nov 13 '13 at 13:52
You can use `OUTPUT` with `UPDATE` as well. (Though `MERGE` can [sometimes](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id) be more flexible) — Martin Smith, Nov 13 '13 at 13:55
Yeah, OUTPUT works great with all DML operations, but I was referring to the exception with INSERT which doesn't allow the OUTPUT of columns/values not present in the target table. I often find the need to output values from joining tables to produce a meaningful result, which is where MERGE comes in handy. :) — Kahn, Nov 13 '13 at 14:05

score 3 · Answer 1 · answered Nov 13 '13 at 13:10

3

MERGE is useful to choose between an INSERT or an UPDATE depending of the presence of data in the table. If you do only an UPDATE, you can keep doing an UPDATE.

answered Nov 13 '13 at 13:10

rudi bruchez

624
3
10

score 1 · Answer 2 · answered Nov 13 '13 at 13:19

1

Merge is an aka for "Upsert". Aka, Update and Insert logic in the same "call".

http://en.wikipedia.org/wiki/Upsert

If you use the aka word (upsert that is), it'll help you when picking the right tool for the right job.

answered Nov 13 '13 at 13:19

granadaCoder

26,328
10
113
146

using merge for updating

2 Answers2