How to UPDATE TOP(n) with ORDER BY giving a predictable result?

Question

I'm trying to read the top 100 items of a database table that is being used like a queue. As I do this I'm trying to mark the items as done like this:

UPDATE TOP(@qty)
    QueueTable WITH (READPAST)  
SET 
    IsDone = 1
OUTPUT
    inserted.Id,
    inserted.Etc
FROM
    QueueTable 
WHERE
    IsDone = 0
ORDER BY
    CreatedDate ASC;

The only problem is, according to UPDATE (Transact-SQL) on MSDN, the ORDER BY is not valid in an UPDATE and:

The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

How can I achieve what I need which is to update the items at the top of the queue while also selecting them?

Answer 1

SQL Server allows you to update a derived table, CTE or view:

UPDATE x
SET 
    IsDone = 1
OUTPUT
    inserted.Id,
    inserted.Etc
FROM (
select TOP (N) *
FROM
    QueueTable 
WHERE
    IsDone = 0
ORDER BY
    CreatedDate ASC;
) x

No need to compute a set of IDs first. This is faster and usually has more desirable locking behavior.