i’ve been asked by lost of people about how to use CASE when you write a MERGE statement which is synchronizing (updating) two tables.
So here we go (a very basic example which sync the quantity field in the Products table):
-- DELETES THE DUPLICATES IF ANY! (THIS IS A MUST WHEN YOU USE MERGE) DELETE everything FROM ( SELECT *, rownum = ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY ItemID) FROM QuantityUpdates ) everything WHERE rownum > 1; -- please notice that you can also use group by to prevent duplicate rows !!! MERGE INTO Products AS Prod USING QuantityUpdates AS Qty ON Prod.ItemID = Qty.ItemID WHEN MATCHED THEN UPDATE SET QtyInStock = Qty.QtyInStock, UpdatedDateStamp = GETDATE(), Discontinued = CASE WHEN Qty.QtyInStock > 0 THEN 0 ELSE 1 END;
Voila! Hope this helps someone