7

Suppose you have something like this:

Source table (variable):

Values (
  LeftId INT NOT NULL,
  RightId INT NOT NULL,
  CustomValue varchar(100) NULL
)

Target table:

Mapping (
  LeftId INT NOT NULL,
  RightId INT NOT NULL,
  CustomValue varchar(100) NULL
)

I want to merge Values into Target with the following rules:

  • Match on source.LeftId = target.LeftId AND source.RightId = target.RightId
    • when matched in target, update CustomValue
    • when not matched in target, insert
  • Delete any unmatched values in the target that do match a LeftId in the source, i.e. only delete records that related to the LefIds of what I'm merging.

(That last rule is hard to describe, sorry!)

For instance:

Source:

1, 10, foo
1, 11, foo

Target:

1, 10, bar
1, 12, foo
2, 20, car

Merge result:

Result Target:

1, 10, foo (updated)
1, 11, foo (inserted)
1, 12, foo (deleted)
2, 20, car (unchanged)

So...

Here's what I have so far, which takes care of update and insert:

MERGE Mapping AS target
USING (SELECT LeftId, RightId, CustomValue FROM @Values) 
  AS source (LeftId, RightId, CustomValue)
  ON target.LeftId = source.LeftId
    AND target.RightId = source.RightId
WHEN NOT MATCHED THEN
  INSERT (LeftId, RightId, CustomValue)
  VALUES (source.LeftId, source.RightId, source.CustomValue)
WHEN MATCHED THEN
  UPDATE SET
    CustomValue = source.CustomValue;

How do I do the delete part of my rule?

2
  • There is no "matched on one column but not the other" branch, and the "not matched" rule you want to add seems to rely on a unique seek on the LeftID values in @Values. Why not perform this operation in a second statement instead of trying to shoehorn it into MERGE? Concurrency / race condition protections aren't guaranteed in MERGE without an explicit HOLDLOCK anyway, and if you create multiple statements you can achieve the same effect with a wrapping transaction. Aug 13, 2013 at 17:26
  • 1
    @AaronBertrand, it feels like that's the right way to go. I think I can get the effect I want with this, but as you say, it may not be appropriate: WHEN NOT MATCHED BY source AND EXISTS(SELECT * FROM @Values M WHERE M.LeftId = target.LeftId) THEN DELETE; Aug 13, 2013 at 17:31

4 Answers 4

6

This is the separate DELETE operation I had in mind:

DELETE m
FROM dbo.Mapping AS m
WHERE EXISTS 
  (SELECT 1 FROM @Values WHERE LeftID = m.LeftID)
AND NOT EXISTS 
  (SELECT 1 FROM @Values WHERE LeftID = m.LeftID AND RightID = m.RightID);

As I outline here, for a left anti-semi join, the NOT EXISTS pattern will often outperform the LEFT JOIN / NULL pattern (but you should always test).

Not sure if your overall goal is clarity or performance, so only you can judge if this will work out better for your requirements than the NOT MATCHED BY source option. You'll have to look at the plans qualitatively, and the plans and/or runtime metrics quantitatively, to know for sure.

If you expect your MERGE command to protect you from race conditions that would happen with multiple independent statements, you better make sure that is true by changing it to:

MERGE dbo.Mapping WITH (HOLDLOCK) AS target

(From Dan Guzman's blog post.)

Personally, I would do all of this without MERGE, because there are unresolved bugs, among other reasons. And Paul White seems to recommend separate DML statements as well.

And here's why I added a schema prefix: you should always reference objects by schema, when creating, affecting, etc.

0
10

You can filter out the rows you need to consider from the target table in a CTE and use the CTE as the target in the merge.

WITH T AS
(
  SELECT M.LeftId, 
         M.RightId, 
         M.CustomValue
  FROM @Mappings AS M
  WHERE EXISTS (SELECT *
                FROM @Values AS V
                WHERE M.LeftId = V.LeftId) 
)
MERGE T
USING @Values AS S
ON T.LeftId = S.LeftId and
   T.RightId = S.RightId
WHEN NOT MATCHED BY TARGET THEN
  INSERT (LeftId, RightId, CustomValue) 
  VALUES (S.LeftId, S.RightId, S.CustomValue)
WHEN MATCHED THEN
  UPDATE SET CustomValue = S.CustomValue
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
;
5
  • 2
    Using a CTE for this is warned against in BOL apparently though I'm not clear about when exactly it might behave differently from a view. Aug 13, 2013 at 18:17
  • @MartinSmith Is that not a bit strange? I would think that CTE's view's expand into the query in the same way. Apparently there is some difference in how they are treated. Aug 13, 2013 at 18:35
  • @MikaelEriksson - I'd regarded them as being interchangeable as well prior to reading that. Aug 13, 2013 at 20:50
  • @MartinSmith BOL describes best practices so you will not do silly mistakes when writing the merge statement. Once you realize that the on clause is a full outer join you understand why strange things happen when you filter on constants in the on clause. Same thing with the CTE. It can't do anything you are not asking it to do but it is hard to spot the cases where you have messed up. What is described as a problem with the CTE is when you filter out rows from the target that would have matched the source and would have prevented an insert. Aug 14, 2013 at 11:30
  • @MartinSmith And it is the same with views unless you specify with check option. Then the insert "by misstake" is prevented. So there is the difference between the view and the CTE. They just forgot to mention in BOL that it is dependent on with check option. SQL Fiddle Aug 14, 2013 at 11:31
3

You can use the WHEN NOT MATCHED BY SOURCE clause and provide an additional condition with it like this:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.Vals (
  LeftId INT NOT NULL,
  RightId INT NOT NULL,
  CustomValue varchar(100) NULL
);

CREATE TABLE dbo.Mapping (
  LeftId INT NOT NULL,
  RightId INT NOT NULL,
  CustomValue varchar(100) NULL
);

INSERT INTO dbo.Vals(LeftId,RightId,CustomValue)
VALUES(1, 10, 'foo10'),(1, 11, 'foo11');

INSERT INTO dbo.Mapping(LeftId,RightId,CustomValue)
VALUES(1, 10, 'bar'),(1, 12, 'foo'),(2, 20, 'car');

Query 1:

MERGE dbo.Mapping WITH(HOLDLOCK) AS target
USING (SELECT LeftId, RightId, CustomValue FROM dbo.Vals) 
  AS source (LeftId, RightId, CustomValue)
  ON target.LeftId = source.LeftId
    AND target.RightId = source.RightId
WHEN NOT MATCHED THEN
  INSERT (LeftId, RightId, CustomValue)
  VALUES (source.LeftId, source.RightId, source.CustomValue)
WHEN MATCHED THEN
  UPDATE SET
    CustomValue = source.CustomValue


WHEN NOT MATCHED BY SOURCE AND EXISTS(SELECT 1 FROM dbo.Vals iVals WHERE target.LeftId = iVals.LeftId) THEN
  DELETE



OUTPUT $action AS Action,
       INSERTED.LeftId AS INS_LeftId,INSERTED.RightId AS INS_RightId,INSERTED.CustomValue AS INS_Val,
       DELETED.LeftId AS DEL_LeftId,DELETED.RightId AS DEL_RightId,DELETED.CustomValue AS DEL_Val;

Results:

| ACTION | INS_LEFTID | INS_RIGHTID | INS_VAL | DEL_LEFTID | DEL_RIGHTID | DEL_VAL |
------------------------------------------------------------------------------------
| INSERT |          1 |          11 |   foo11 |     (null) |      (null) |  (null) |
| UPDATE |          1 |          10 |   foo10 |          1 |          10 |     bar |
| DELETE |     (null) |      (null) |  (null) |          1 |          12 |     foo |

Query 2:

SELECT * FROM dbo.Mapping;

Results:

| LEFTID | RIGHTID | CUSTOMVALUE |
----------------------------------
|      1 |      10 |       foo10 |
|      2 |      20 |         car |
|      1 |      11 |       foo11 |

I added the output clause to the MERGE statement to show what action was taken for each row.

As others have commented, you need to also provide the WITH(HOLDLOCK) hint on the target table to prevent race conditions.

1

Here's what I've come up with. Any feedback is appreciated!

-- Test data:

DECLARE @Values   TABLE(LeftId INT, RightId INT, CustomValue VARCHAR(100))
DECLARE @Mappings TABLE(LeftId INT, RightId INT, CustomValue VARCHAR(100))

-- the incoming values
INSERT INTO @Values   VALUES (1, 10, 'bar2'), (1, 11, 'foo')

-- the existing table
INSERT INTO @Mappings VALUES (1, 10, 'bar'),  (1, 12, 'foo'), (2, 20, 'car')

-- Option 1: Handle the delete part separately:

DELETE M
FROM @Mappings M
JOIN (SELECT DISTINCT LeftId FROM @Values) DistinctLeftIds ON M.LeftId = DistinctLeftIds.LeftId 
LEFT JOIN @Values V ON M.LeftId = V.LeftId AND M.RightId = V.RightId
WHERE V.LeftId IS NULL

MERGE @Mappings AS target
USING (SELECT LeftId, RightId, CustomValue FROM @Values) 
  AS source (LeftId, RightId, CustomValue)
  ON target.LeftId = source.LeftId
    AND target.RightId = source.RightId
WHEN NOT MATCHED THEN
  INSERT (LeftId, RightId, CustomValue)
  VALUES (source.LeftId, source.RightId, source.CustomValue)
WHEN MATCHED THEN
  UPDATE SET
    CustomValue = source.CustomValue;

-- Option 2: Do it (awkwardly?) in the MERGE statement:

MERGE @Mappings AS target
USING (SELECT LeftId, RightId, CustomValue FROM @Values) 
  AS source (LeftId, RightId, CustomValue)
  ON target.LeftId = source.LeftId
    AND target.RightId = source.RightId
WHEN NOT MATCHED THEN
  INSERT (LeftId, RightId, CustomValue)
  VALUES (source.LeftId, source.RightId, source.CustomValue)
WHEN MATCHED THEN
  UPDATE SET
    CustomValue = source.CustomValue;
WHEN NOT MATCHED BY source 
    AND EXISTS(SELECT * FROM @Values M WHERE M.LeftId = target.LeftId) THEN
  DELETE;

-- Check results:

SELECT * FROM @Mappings ORDER BY LeftId, RightId

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.