Is it possible to update multiple columns using different criteria in one statement?

Question

I need to set all columns to 0 if they are negative. Currently I do it one column at a time:

UPDATE #ListingRank SET Distance = 0 WHERE Distance < 0
UPDATE #ListingRank SET ActionDays = 0 WHERE ActionDays < 0
UPDATE #ListingRank SET Price = 0 WHERE Price < 0
UPDATE #ListingRank SET Beds = 0 WHERE Beds < 0
UPDATE #ListingRank SET Baths = 0 WHERE Baths < 0
UPDATE #ListingRank SET SqFt = 0 WHERE SqFt < 0

Is it possible to do the same in a single statement?

Answer 1

You can try to use CASE WHEN

UPDATE #ListingRank
SET Distance = (CASE WHEN Distance < 0 THEN 0 ELSE Distance END),
    ActionDays = (CASE WHEN ActionDays < 0 THEN 0 ELSE ActionDays END),
    Price = (CASE WHEN Price < 0 THEN 0 ELSE Price END),
    Beds = (CASE WHEN Beds < 0 THEN 0 ELSE Beds END),
    Baths = (CASE WHEN Baths < 0 THEN 0 ELSE Baths END),
    SqFt = (CASE WHEN SqFt < 0 THEN 0 ELSE SqFt END)