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?
MERGE
, use a transaction with oneINSERT
and twoUPDATE
, all three of which have aWHERE
clause that mimic the clauses in the original merge?