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?
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.