Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  Aaron Bertrand Aug 13 '13 at 17:26
    
@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; –  Michael Haren Aug 13 '13 at 17:31

4 Answers 4

up vote 2 down vote accepted

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.

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.

share|improve this answer
    
Thank you for the thoughtful reply, and helpful references :) –  Michael Haren Aug 15 '13 at 18:38

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
;
share|improve this answer
    
+1 nice workaround (forest for the trees), and good shorthanding the source. –  Aaron Bertrand Aug 13 '13 at 18:04
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. –  Martin Smith Aug 13 '13 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. –  Mikael Eriksson Aug 13 '13 at 18:35
    
@MikaelEriksson - I'd regarded them as being interchangeable as well prior to reading that. –  Martin Smith Aug 13 '13 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. –  Mikael Eriksson Aug 14 '13 at 11:30

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.

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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