Update Query in SQL using nested Case

Question

I wanted to have a following update query using nested case;

UPDATE mstsales 
SET test = '2017-18'
WHERE salemonth > 3 AND saleyear = 2017 OR salemonth < 4 AND saleyear = 2018  

UPDATE mstsales 
SET test = '2018-19'
WHERE salemonth > 3 AND saleyear = 2018 OR salemonth < 4 AND saleyear = 2019  

UPDATE mstsales 
SET test = '2019-20'
WHERE salemonth > 3 AND saleyear = 2019 OR salemonth < 4 AND saleyear = 2020

Use a case expression in the SET clause, and adjust the WHERE clause to the first start and the last end period. — jarlh, May 22 '20 at 12:30
If you did a search a bit: https://stackoverflow.com/questions/15766102/i-want-to-use-case-statement-to-update-some-records-in-sql-server-2005/15766129 — Srinika Pinnaduwage, May 22 '20 at 12:35
@KarikalValavan . . . Tag your question with the database you are using. — Gordon Linoff, May 22 '20 at 12:37

score 1 · Answer 1 · answered May 22 '20 at 12:37

You can do:

UPDATE mstsales 
    SET test = (CASE WHEN salemonth > 3 and saleyear = 2017 or salemonth < 4 and saleyear = 2018 THEN '2017-18'
                     WHEN salemonth > 3 and saleyear = 2018 or salemonth < 4 and saleyear = 2019  THEN '2018-19'
                     WHEN salemonth > 3 and saleyear = 2019 or salemonth < 4 and saleyear = 2020 
                END)
WHERE (salemonth > 3 and saleyear = 2017 or salemonth < 4 and saleyear = 2018) OR 
      (salemonth > 3 and saleyear = 2018 or salemonth < 4 and saleyear = 2019) OR
      (salemonth > 3 and saleyear = 2019 or salemonth < 4 and saleyear = 2020)

I don't think this simplifies the logic.

You don't mention your database. But there are cleaner alternatives:

Use a computed column.
Use a reference table.
Use a derived table for the update.

score 0 · Answer 2 · answered May 22 '20 at 12:39

Hope this helps..

UPDATE mstsales
SET     test =  CASE  
                        WHEN salemonth > 3 and saleyear = 2017 or salemonth < 4 and saleyear = 2018  THEN '2017-18' 
                        WHEN salemonth > 3 and saleyear = 2018 or salemonth < 4 and saleyear = 2019  THEN '2018-19' 
                        WHEN salemonth > 3 and saleyear = 2019 or salemonth < 4 and saleyear = 2020  THEN '2019-20' 
                    END

If there is common condition for all the cases, move into where clause.

Update Query in SQL using nested Case

2 Answers2