Similar to this question, but I require this in Microsoft SQL Server.

I'm looking to update values in my database, but only the most recently added value for each date.

For example if I have 4 rows where column_day = '2023-02-01',
and they have updated datetimes in a column [Time_Stamp] of
2023-01-01, 2023-01-27, 2023-01-10, and 2023-01-05,
I would like to only update the Jan 27, 2023 2023-01-27 line.

The answer provided here which does not work in Microsoft SQL Server is:

UPDATE your_table
SET some_column = 1
ORDER BY date_time_column DESC
LIMIT 1

My code I'm trying is slightly different as it includes a WHERE:
I get an error

Incorrect syntax near the keyword 'order'

UPDATE your_table
SET some_column = 1
WHERE column_tag = 'test' AND column_day = '2023-02-01'
ORDER BY Time_Stamp DESC
LIMIT 1

How can I achieve this in Microsoft SQL Server?

CodePudding user response：

You can use TOP in an UPDATE, however, you can't provide an ORDER BY; as such it's really used for batching and previously UPDATEd rows are filtered in each iteration in the WHERE clause.

For what you want, one method would be to use an UPDATEable CTE with ROW_NUMBER:

WITH CTE AS(
    SELECT SomeColumn,
           ROW_NUMBER() OVER (ORDER BY date_time_column DESC) AS RN
    FROM dbo.YourTable
    WHERE ColumnTag = 'Test'
      AND ColumnDay = '20230201')
UPDATE CTE
SET SomeColumn = 1
WHERE RN = 1;