MERGE with WHERE clause

Question

Consider data existing in a table:

Customers

| CustomerID | Name            | Status             |
|------------|-----------------|--------------------|
|          1 | Ian Boyd        | Killed             |
|          2 | Shelby Hawthorn | Booked             |

And rows i would like to MERGEd into the Customers table:

| CustomerID | Name            | Status             |
|------------|-----------------|--------------------|
|          1 | Ian Boyde       | Waiting            | name has 'e' on the end
|          2 | Shelby Blanken  | Waiting            | different last name
|          3 | Jessica Bogden  | Waiting            | totally new row

So i can come up with approximate psuedocode MERGE statement:

MERGE Customers USING (
    SELECT CustomerID, Name, 'Waiting' FROM Staging) foo
ON Customers.CustomerID = foo.CustomerID
WHEN MATCHED THEN
   UPDATE SET Name = foo.Name, Status = foo.Status
WHEN NOT MATCHED BY TARGET THEN
   INSERT (Name, Status) 
   VALUES (Name, Status);

And that would MERGE them:

| CustomerID | Name            | Status             |
|------------|-----------------|--------------------|
|          1 | Ian Boyde       | Waiting            | Last name spelling updated
|          2 | Shelby Blanken  | Waiting            | Last name changed
|          3 | Jessica Bogden  | Waiting            | New row added

But only UPDATE some rows

Except a caveat is that i don't want update any existing rows for customers who are Booked. In other words i want the final results to be:

| CustomerID | Name            | Status             |
|------------|-----------------|--------------------|
|          1 | Ian Boyde       | Waiting            | updated existing row spelling
|          2 | Shelby Hawthorn | Booked             | not updated because they're booked
|          3 | Jessica Bogden  | Waiting            | inserted new row

My first guess would for the UPDATE to have a where clause:

MERGE Customers USING (
    SELECT CustomerID, Name, 'Waiting' FROM Staging) foo
ON Customers.CustomerID = foo.CustomerID
WHEN MATCHED THEN
   UPDATE SET Name = foo.Name, Status = foo.Status
   WHERE Status <> 'Booked' -- <--------- it's the matching row; but don't update it
WHEN NOT MATCHED BY TARGET THEN
   INSERT (Name, Status) 
   VALUES (Name, Status);

But that's not a valid syntax.

My second guess would be to add the criteria to the ON clause:

MERGE Customers USING (
    SELECT CustomerID, Name, 'Waiting' FROM Staging) foo
ON Customers.CustomerID = foo.CustomerID
AND Customers.Status <> 'Booked'
WHEN MATCHED THEN
   UPDATE SET Name = foo.Name, Status = foo.Status
   WHERE Status <> 'Booked' --it's the matching row; but don't update it
WHEN NOT MATCHED BY TARGET THEN
   INSERT (Name, Status) 
   VALUES (Name, Status);

But now the row would not match, and they would get inserted under the not matched by target rule:

| CustomerID | Name            | Status             |
|------------|-----------------|--------------------|
|          1 | Ian Boyde       | Waiting            | updated existing row
|          2 | Shelby Hawthorn | Booked             | not matched bcause booked
|          3 | Jessica Bogden  | Waiting            | inserted new row
|          4 | Shelby Blanden  | Waiting            | Mistakenly inserted because not matched by target

What's the way out of the conundrum?

score 2 · Accepted Answer · answered Sep 10 '19 at 17:41

The key is that you want to make sure that the record falls into the MATCHED logic, otherwise it will generate a new row via the NOT MATCHED logic.

To do this, using your code, we add your criteria to the MATCHED logic:

MERGE Customers USING (
    SELECT CustomerID, Name, 'Waiting' FROM Staging) foo
ON Customers.CustomerID = foo.CustomerID
WHEN MATCHED AND Customers.Status <> 'Booked' THEN
   UPDATE SET Name = foo.Name, Status = foo.Status
WHEN NOT MATCHED BY TARGET THEN
   INSERT (Name, Status) 
   VALUES (Name, Status);

This tells the merge to match everything on CustomerID. When it finds a match, you then tell it to only run the update if the Status <> 'Booked'

Caius Jard · Answer 2 · 2019-09-10T17:38:39.093

Merge statements are driven by the USING clause.

Rows in the USING that do match existing rows cause existing rows to update.
Rows in the USING that do not match existing rows cause new rows to be created
Rows that are not in the USING clause cannot affect rows in the db

If you do not want an existing row to be updated, ensure its matching row never makes it into the result set presented by the statement in the USING. This may mean doing a join in the USING. This is fine

Example:

MERGE Customers USING (
  SELECT 
    s.CustomerID, 
    s.Name,
    'Waiting' as Stat
  FROM 
    Staging s
    INNER JOIN Existing e on s.CustomerId = e.CustomerId
  WHERE
    e.Status <> 'Booked' --ignore all existing booked rows
) foo
...

This join inside the using statement ensures that the staging row that relates to the existing "Booked" row, never makes it into the result set produced by the USING. It hence cannot cause either an update or an insert

MERGE with WHERE clause

But only UPDATE some rows

2 Answers2