Jonathan Porter Jonathan Porter - 27 days ago 5
SQL Question

Can I combine multiple update statements while keeping functionality?

I'm going through a stored procedure and I was trying to optimize it when I came across about 13 update statements similar to below. I was wondering if anyone had a better solution than this. I know I can use case statements or coalesce and this would be semantically the same, but just bear in mind that all of the columns will always be updated. This probably won't cause you any problems, but I may have a high transactional volume so this approach could cause concurrency issues.

UPDATE COGS
SET Revenue = 0
WHERE Revenue IS NULL
AND monthDate = @targetCOGSdate;

UPDATE COGS
SET Costs = 0
WHERE Costs IS NULL
AND monthDate = @targetCOGSdate;

UPDATE COGS
SET Disposal = 0
WHERE Disposal IS NULL
AND monthDate = @targetCOGSdate;

UPDATE COGS
SET Trans = 0
WHERE Trans IS NULL
AND monthDate = @targetCOGSdate;

Answer

Yes, use COALESCE():

UPDATE COGS
    SET Revenue = COALESCE(Revenue, 0),
        Costs = COALESCE(Costs, 0),
        Disposal = COALESCE(Disposal, 0),
        Trans = COALESCE(Trans, 0)
    WHERE monthDate = @targetCOGSdate AND
          (Revenue IS NULL OR Costs IS NULL OR Disposal IS NULL OR Trans IS NULL);