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?