MERGE a subset of the target table — Pitfall and Options


I have used MERGE for sometime now. It is a very useful feature added to SQL Server 2008.  I have used MERGE to come up with some solutions to my real production code.

When it is time to use a subset of the target table to manipulate, it is attempting to add the condition to the ON clause like target.id=2 to filter our some records for the target table. But this is not the correct way to do it.

I have run into this issue for a question posted at MSDN:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/38e82c13-af2f-45d2-98f6-a77c686ef6f4/update-column-with-values-from-the-same-column#4d037492-f0ec-4c26-aaf3-bfcc4b52aada

Tom Cooper pointed out the issue in another related thread:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a2ad67c-bff5-4dba-bf71-1b8004be90ce/update-column-with-values-from-the-same-column#6ebd6bdb-7bd9-4989-bc89-62d378a6adc2

 

To better understand this issue, I serached to find a detailed example from Paul White to discussing about this issue: http://dba.stackexchange.com/questions/30633/merge-a-subset-of-the-target-table

Here is a fully documented link from MSDN:  http://technet.microsoft.com/en-us/library/bb522522%28v=sql.105%29.aspx

and the definition of MERGE documentation: http://technet.microsoft.com/en-us/library/bb510625(v=sql.105).aspx

 

To solve the problem, we have three options: Use the filter at the WHEN clause; use an UPDATABLE View; use CTE.

I would like to use WHEN if I want a quick fix. To make it more clear, I will write the code starting with a CTE block before the MERGE keyword and use the CTE  as target table.

 

 

 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s