MERGE a subset of the target table — Pitfall and OptionsPosted: July 10, 2013
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:
Tom Cooper pointed out the issue in another related thread:
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.