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

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?

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.

StackExchange Database Administrators Q#116615, answer score: 7