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.

Answer 2

You can try sub query like

  UPDATE Messages      SET    status = 'P'      WHERE MessageId IN (SELECT TOP (@MaxRecords) MessageId FROM Messages where Status = 'N' and InsertDate >= GETDATE() ORDER BY Priority) output inserted.*