SQL Server: UPDATE based on whether a value is greater or lesser than another value, in a single que-CodePudding

I'm looking to update SQL Sever 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'

Is there a way in SQL server to combine these two queries in to a single update query?

EDIT: to show the SET values are strings.

CodePudding user response：

Try something like this

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

CodePudding user response：

You may use CASE expression:

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