'SQL Server: UPDATE based on whether a value is greater or lesser than another value, in a single query

I'm looking to update SQL Server in a single query based on a date, however the value updated depends on whether it is greater or lower than a provided value (in this scenario a date).

UPDATE table 
SET id = 'over'
WHERE date > '2022-01-01'

UPDATE table
SET id = 'under'
WHERE date < '2022-01-01'

I can do this individually via the queries above. My question is, is there a way in SQL Server to combine these two queries and run this update in a single update query?

EDIT: to show the SET values are strings.

sqlsql-server


Solution 1:[1]

Try something like this

UPDATE table 
SET id = case 
   when (date > '2022-01-01') then over
   when (date < '2022-01-01') then under
   end;

Solution 2:[2]

You may use CASE expression to evaluate all possible conditions and return the appropriate values for each condition. Note, that if you omit the ELSE part of the CASE and no condition evaluates to TRUE, the result is NULL.

UPDATE [table]
SET [id] = CASE 
              WHEN [date] > '20220101' THEN 'over'
              WHEN [date] < '20220101' THEN 'under'
              ELSE ''
           END

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Volnei Munhoz
Solution 2

Related Questions