How can I avoid repeating the same condition in a "when matched then update" clause?

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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
…

Answer 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;