5

I want to merge one table into another. I need to apply conditional logic in my WHEN MATCHED clause, which would ideally be done like this:

MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Do insert
WHEN MATCHED AND b.NeedsAdjustment = 1 THEN
UPDATE SET
    Col1 = b.Col1 + b.Adjustment
    ,Col2 = b.Col2 + b.Adjustment
    ,Col3 = b.Col3 + b.Adjustment
WHEN MATCHED THEN -- Default case (b.NeedsAdjustment <> 1)
UPDATE SET
    Col1 = b.Col1
    ,Col2 = b.Col2
    ,Col3 = b.Col3

This is not valid SQL. According to the MSDN documenation:

If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE action.

This leads me to the following query:

MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Insert happens here
WHEN MATCHED THEN
UPDATE SET
    Col1 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col1 ELSE b.Col1 + b.Adjustment END
    ,Col2 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col2 ELSE b.Col2 + b.Adjustment END
    ,Col3 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col3 ELSE b.Col3 + b.Adjustment END

The conditional logic is moved inside of the update to get around the fact that merges can only have one WHEN MATCHED THEN UPDATE clause. Now, instead of one check per row, I have one check per row per column (and there are many more columns than the three in the example).

Can I avoid repeating this condition for every column that needs to be updated? Is there a better way to do conditional updates that perhaps don't involve merges?

  • If you're just using the MATCHED portion of the MERGE statement, write two separate UPDATEs – Dave Sep 30 '15 at 15:41
  • @Dave You mean like the first example in the question? – Rainbolt Sep 30 '15 at 15:43
  • Essentially... if you're using a merge to keep it in the same transaction just wrap the two UPDATE statements in an explicit transaction. – Dave Sep 30 '15 at 15:55
  • @Dave I think I understand what you are saying. Are you suggesting that instead of using a single MERGE, use a transaction with one INSERT and two UPDATE, all three of which have a WHERE clause that mimic the clauses in the original merge? – Rainbolt Sep 30 '15 at 16:36
4

MERGE is a handy statement, but limited in situations such as this. I'd suggest simply breaking apart your query so that you can completely cover your logic. If you were expecting or required the fact that the MERGE statement will perform it's operations in a single transaction, you can declare an explicit transaction around your logic:

BEGIN TRAN
INSERT
WHERE

UPDATE
WHERE

UPDATE
WHERE

COMMIT TRAN
  • 1
    It took some work to figure out how to transform the WHEN NOT MATCHED into a WHERE clause. In the example I posted, the primary key is just ID, but in reality, the primary key is composite. This answer on Stack Overflow helped me over that hurdle. – Rainbolt Sep 30 '15 at 18:49
7

I would recommend simply avoiding MERGE in the first place for all of the reasons listed here and just simply use a standard update statement.

UPDATE A 
SET
    Col1 = b.Col1 + b.Adjustment
    ,Col2 = b.Col2 + b.Adjustment
    ,Col3 = b.Col3 + b.Adjustment
FROM ATable as A
inner Join TableB as B
    ON a.ID = b.ID
    AND b.NeedsAdjustment = 1
;
UPDATE A 
SET
    Col1 = b.Col1 
    ,Col2 = b.Col2
    ,Col3 = b.Col3
FROM ATable as A
inner Join TableB as B
    ON a.ID = b.ID
AND b.NeedsAdjustment <> 1
--Or INSNUL(NeedsAdjustment,0) If it's a nullable column
;

In the long run this would be a more stable approach than using MERGE. I've also added the <> because if that's not there your UPDATE will overwrite the first UPDATE.

  • 3
    @ypercube I didn't make assumptions for that. Since his original had that commented out. For all I know NULL's are against OP's Religion. – Zane Sep 30 '15 at 17:21
4

If NeedsAdjustment is a bit column, or if it is an integer column that can only have values 0 or 1, then you could use a single UPDATE statement like this to cover both cases:

UPDATE
  a
SET
  a.Col1 = b.Col1 + b.NeedsAdjustment * b.Adjustment,
  a.Col2 = b.Col2 + b.NeedsAdjustment * b.Adjustment,
  a.Col3 = b.Col3 + b.NeedsAdjustment * b.Adjustment
FROM
  dbo.ATable AS a
  INNER JOIN dbo.BTable AS b ON a.ID = b.ID
;

If the column is nullable, additionally apply ISNULL or COALESCE:

…
SET
  a.Col1 = b.Col1 + ISNULL(b.NeedsAdjustment, 0) * b.Adjustment,
  a.Col2 = b.Col2 + ISNULL(b.NeedsAdjustment, 0) * b.Adjustment,
  a.Col3 = b.Col3 + ISNULL(b.NeedsAdjustment, 0) * b.Adjustment
…
4

Using a CTE, you can "stage" the data that's in BTable so you can use it uniformly in the MERGE statement. In this case, if a row does not need to be adjusted, change Adjustment to zero.

WITH b (Col1, Col2, Col3, Adjustment)
AS
(
    SELECT Col1, Col2, Col3, CASE WHEN NeedsAdjustment = 1 THEN Adjustment ELSE 0 END
    FROM BTable
)
MERGE INTO ATable AS a
USING b
    ON a.ID = b.ID
WHEN NOT MATCHED THEN
    -- Insert happens here
WHEN MATCHED THEN
    UPDATE SET
        Col1 = b.Col1 + b.Adjustment,
        Col2 = b.Col2 + b.Adjustment,
        Col3 = b.Col3 + b.Adjustment;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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