How do I merge three update query into one single update query I am trying to update a promotion table with a single update statement. Although the three update statements below can do it, I want to merge the three statements into one. How can I do that?
UPDATE promos SET next_promotion = (last_promotion + 4) WHERE grade_level IN (14,15,16,17,18)
UPDATE promos SET next_promotion = (last_promotion + 3) WHERE grade_level IN (7,8,9,10,11,12,13)
UPDATE promos SET next_promotion = (last_promotion + 2) WHERE grade_level IN (1,2,3,4,5,6)
You could do it with a CASE statement like so:
UPDATE P
SET P.next_promotion = 
     CASE 
         WHEN P.grade_level IN (14,15,16,17,18) THEN P.last_promotion + 4
         WHEN P.grade_level IN (7,8,9,10,11,12,13) THEN P.last_promotion + 3
         WHEN P.grade_level IN (1,2,3,4,5,6) THEN P.last_promotion + 2
         ELSE P.next_promotion
     END
FROM Promos AS P
WHERE P.grade_level &gt;= 1 
AND P.grade_level &lt;= 18 -- Ensures we only update the records we want to change and doesn't do any extra work updating

And this equivalent query minimizes the code (if you're counting characters :).
UPDATE Promos
SET next_promotion = 
     CASE 
         WHEN grade_level BETWEEN 14 AND 18 THEN last_promotion + 4
         WHEN grade_level BETWEEN 7 AND 13 THEN last_promotion + 3
         WHEN P.grade_level BETWEEN 1 AND 6 THEN last_promotion + 2
         ELSE next_promotion
     END
WHERE grade_level BETWEEN 1 AND 18 -- Ensures we only update the records we want to change and doesn't do any extra work updating