How to update and order by using ms sql

Question

Ideally I want to do this:

UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC; 

In English: I want to get the top 10 available (status=0) messages from the DB and lock them (status=10). A message with a higher priority should be gotten first.

unfortunately MS SQL doesn't allow an order by clause in the update.

Anyway how to circumvent this?

Answer 1

WITH    q AS         (         SELECT  TOP 10 *         FROM    messages         WHERE   status = 0         ORDER BY                 priority DESC         ) UPDATE  q SET     status = 10