sql server - SQL UPDATE TOP with ORDER BY?

Question

I have a following query:

UPDATE TOP (@MaxRecords) Messages 
SET    status = 'P' 
OUTPUT inserted.* 
FROM   Messages 
where Status = 'N'
and InsertDate >= GETDATE()

In the Messages table there is priority column and I want to select high priority messages first. So I need an ORDER BY. But I do not need to have sorted output but sorted data before update runs.

As far as I know it's not possible to add ORDER BY to UPDATE statement. Any other ideas?

Answer 1

you can use common table expression for this:

;with cte as (
   select top (@MaxRecords)
       status
   from Messages 
   where Status = 'N' and InsertDate >= getdate()
   order by ...
)
update cte set
    status = 'P'
output inserted.*

This one uses the fact that in SQL Server it's possible to update cte, like updatable view.