The MERGE statement doesn’t have a WHERE clause.

5

September 30, 2013 by Kenneth Fisher

What the heck? Even indexes have WHERE clauses these days. I can’t remember what I was reading when I saw this but it completely flabbergasted me. If you go look at MERGE in BOL you will see what I mean. So does this mean you can’t restrict data in a MERGE?

No, of course not. There are in fact several different ways I know of off-hand that you can handle it. You can use subqueries and CTEs to modify your source or you can add search conditions to your WHEN MATCHED and WHEN NOT MATCHED clauses.

Here is an example.

Adventureworks is having a major reorg. First we set up the update table (note this is just a sample of the extensive changes they are making).

USE AdventureWorks2008;
GO
SELECT TOP 0 CAST(NULL AS smallint) AS DepartmentID, Name, GroupName
	INTO dbo.DepartmentReorg 
FROM HumanResources.Department;
GO
INSERT INTO dbo.DepartmentReorg VALUES (10, 'Accounting', 'Executive General and Administration');
INSERT INTO dbo.DepartmentReorg VALUES (11, 'Information Technology', 'Executive General and Administration');
INSERT INTO dbo.DepartmentReorg VALUES (12, 'Document Control', 'Executive General and Administration');
INSERT INTO dbo.DepartmentReorg VALUES (13, 'Quality Assurance', 'Executive General and Administration');
INSERT INTO dbo.DepartmentReorg VALUES (NULL, 'Foreign Sales', 'Sales and Marketing');
GO

Now the MERGE statement

MERGE INTO HumanResources.Department Dep
USING DepartmentReorg Reorg
	ON Dep.DepartmentID = Reorg.DepartmentID
WHEN MATCHED THEN
	UPDATE SET Name = Reorg.Name, GroupName = Reorg.GroupName, ModifiedDate = GetDate()
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Name, GroupName, ModifiedDate) VALUES (Reorg.Name, Reorg.GroupName, GetDate());

But wait, management has decided they aren’t ready for the whole reorg yet. They want just the changes to the “Executive General and Administration” group. They don’t want us to get rid of the reorg table since it took weeks to set up correctly, but they do want only the appropriate changes made. So here an example of possible changes to the MERGE command using search commands.

MERGE INTO HumanResources.Department Dep
USING DepartmentReorg Reorg
	ON Dep.DepartmentID = Reorg.DepartmentID
WHEN MATCHED AND Reorg.GroupName = 'Executive General and Administration' THEN
	UPDATE SET Name = Reorg.Name, GroupName = Reorg.GroupName, ModifiedDate = GetDate()
WHEN NOT MATCHED BY TARGET AND Reorg.GroupName = 'Executive General and Administration' THEN
	INSERT (Name, GroupName, ModifiedDate) VALUES (Reorg.Name, Reorg.GroupName, GetDate());

Of course that requires putting the condition in two places, and if it changes it has to be modified in two places. Although this does give you more fine control if you need it, in our case it makes it easier for mistakes to happen. So here is an example of putting the condition in one place using a subquery.

MERGE INTO HumanResources.Department Dep
USING (SELECT *
		FROM DepartmentReorg 
		WHERE GroupName = 'Executive General and Administration') AS Reorg
	ON Dep.DepartmentID = Reorg.DepartmentID
WHEN MATCHED THEN
	UPDATE SET Name = Reorg.Name, GroupName = Reorg.GroupName, ModifiedDate = GetDate()
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Name, GroupName, ModifiedDate) VALUES (Reorg.Name, Reorg.GroupName, GetDate());

Still not my favorite since I find in line queries to be somewhat messy and make the code a bit harder to read. So here is an example using a CTE.

WITH Reorg AS (SELECT *
		FROM DepartmentReorg 
		WHERE GroupName = 'Executive General and Administration')
MERGE INTO HumanResources.Department Dep
USING Reorg
	ON Dep.DepartmentID = Reorg.DepartmentID
WHEN MATCHED THEN
	UPDATE SET Name = Reorg.Name, GroupName = Reorg.GroupName, ModifiedDate = GetDate()
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Name, GroupName, ModifiedDate) VALUES (Reorg.Name, Reorg.GroupName, GetDate());

Take your pick of course.

5 thoughts on “The MERGE statement doesn’t have a WHERE clause.

  1. dave wentzel says:

    Wouldn’t the better place be the ON clause for additional filtering? That’s how I do it.

    MERGE INTO trg
    USING src
    ON trg.col = src.col
    AND src.Something = ‘something else’

  2. Jay says:

    I’ve used a where after the update clause inside a merge once. Not sure if its a ‘good’ practice but it worked 🙂

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,135 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: