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?

Can you use TOP with ORDER BY to get a list of Unique ID's which will be the top 100 (sorted / ordered) rows and only update the table where they contain these IDs? Here's an example with a CTE: http://stackoverflow.com/a/9241466/6167855 — S3S, Nov 17 '16 at 17:18
Does this answer your question? [how can I Update top 100 records in sql server](https://stackoverflow.com/questions/1198364/how-can-i-update-top-100-records-in-sql-server) — Michael Freidgeim, Jun 08 '22 at 11:07

score 9 · Accepted Answer · answered Nov 17 '16 at 17:31

9

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.

answered Nov 17 '16 at 17:31

usr

168,620
35
240
369

2

It's possible. The OP already seems to do that. Any expression at all works. – usr Nov 17 '16 at 17:33
2

You can do select top (@var) ... note that the brackets are required. – Martin Brown Nov 17 '16 at 17:34
I'm not sure which version of SQL Server this was made possible in. – Martin Brown Nov 17 '16 at 17:35
Ok, you are right. Good to know it, I miss the brackets before. – Dance-Henry Nov 17 '16 at 17:35
@usr Do you think it would be necessary to put a WITH (UPDLOCK) on the select to make this thread safe? – Martin Brown Nov 17 '16 at 17:37
1

No, SQL applies the same locking behavior as usual. All of this appears undocumented... but many people use it and I have verified it myself. Updatable views (and equivalently derived tables and CTEs) are a black art to me. – usr Nov 17 '16 at 17:39
@MartinBrown, `top (@Var)` appeared in 2005. Btw, you can even correlate a subquery this way. – Roger Wolf Nov 18 '16 at 00:11

score 0 · Answer 2 · answered Nov 17 '16 at 17:30

Tested in SSMS, it works fine. You may need to do some modification accordingly.

--create table structure
create table #temp1 (
    id int identity(1,1),
    value int
)
go

--insert sample data
insert #temp1 values (1)
go 20


--below is solution
declare @qty int = 10
declare @cmd nvarchar(2000) =
N'update #temp1
set value= 100
output inserted.value
where id in
(
    select top '+ cast(@qty as nvarchar(5)) +' id from #temp1
    order by id
)';

execute sp_executesql @cmd

score 0 · Answer 3 · answered Nov 17 '16 at 18:19

0

You can use ranking function (for example row_number).

update top (100) q
set IsDone = 1
output
    inserted.Id,
    inserted.Etc
from (
    select *, row_number() over(order by CreatedDate asc, (select 0)) rn
    from QueueTable) q
where rn <= 100

answered Nov 17 '16 at 18:19

Deadsheep39

561
3
16

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

3 Answers3