63

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?

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
Toad
  • 15,593
  • 16
  • 82
  • 128

5 Answers5

122
WITH    q AS
        (
        SELECT  TOP 10 *
        FROM    messages
        WHERE   status = 0
        ORDER BY
                priority DESC
        )
UPDATE  q
SET     status = 10
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 2
    +1 for the CTE, doing it this way gets rid of a join for me when using an OUTPUT clause – nitzmahone Nov 19 '10 at 01:51
  • 4
    This would actually be the preferred answer instead of the accepted one. – Carvellis Feb 25 '11 at 11:57
  • 1
    @Haroon: which two statements? – Quassnoi May 10 '12 at 19:08
  • @Quassnoi I think Haroon was saying that in order to be atomic, you would need to wrap the CTE and update with a transaction. – xkingpin May 13 '13 at 13:00
  • 1
    I have to use top 500000 to update all rows in a temporary table, that has no more than 1000 row, to use order by. – Jules Jul 10 '14 at 00:25
  • 3
    **Use semicolon** before with like `;WITH` otherwise you get [this](http://stackoverflow.com/questions/1439123/incorrect-syntax-near-the-keyword-with-previous-statement-must-be-terminated) error , hope helps someone. – Shaiju T Jan 30 '16 at 15:44
55

You can do a subquery where you first get the IDs of the top 10 ordered by priority and then update the ones that are on that sub query:

UPDATE  messages 
SET status=10 
WHERE ID in (SELECT TOP (10) Id 
             FROM Table 
             WHERE status=0 
             ORDER BY priority DESC);
rene
  • 41,474
  • 78
  • 114
  • 152
Eduardo Crimi
  • 1,551
  • 13
  • 13
  • given that I would like an index for this solution. Would I then use: (priority desc, status) or (status, priority desc)? In other words: is the order by used before the where? – Toad Mar 17 '09 at 18:39
  • 7
    Actually the query was not correct.... the order by clause can not be used in a subquery UNLESS a TOP is also given. (This is what the SQL engine says). So the correct query should be: UPDATE messages SET status=10 WHERE ID in (SELECT TOP (10) Id FROM Table WHERE status=0 ORDER BY priority DESC); – Toad Mar 17 '09 at 18:57
  • 1
    You are right, I missed the top when I wrote the final edit. I will edit it as you said – Eduardo Crimi Mar 17 '09 at 19:15
9

I have to offer this as a better approach - you don't always have the luxury of an identity field:

UPDATE m
SET [status]=10
FROM (
  Select TOP (10) *
  FROM messages
  WHERE [status]=0
  ORDER BY [priority] DESC
) m

You can also make the sub-query as complicated as you want - joining multiple tables, etc...

Why is this better? It does not rely on the presence of an identity field (or any other unique column) in the messages table. It can be used to update the top N rows from any table, even if that table has no unique key at all.

Ed Avis
  • 1,350
  • 17
  • 36
mfascino
  • 93
  • 1
  • 4
  • How this answer is different from dotjoe's answer: http://stackoverflow.com/a/655561/2279200 – Athafoud May 22 '17 at 07:34
  • 2
    I am not surprised by the down-vote on my answer if you do not take the time to try to understand it. My answer is different from dotjoe's answer - and most other answers here - because the other answers assume the table has an identity field (...where ID IN...). You cannot always assume the table will have an identity field. I offered an alternative for when you don't have ID field in your table. Please try to understand answer before down-voting. – mfascino May 23 '17 at 13:04
  • Thank you for the brief explanation. Please devote some time to edit your answer and add the detail you just mentioned. It will make your answer more complete and easier to understand without spending too much time on it. Once you edit your answer I will revoke my vote. – Athafoud May 23 '17 at 13:29
  • @Athafoud I've expanded the answer a bit (though I am not the original poster); could you review it please? – Ed Avis Aug 31 '17 at 14:44
  • From all of the other options, this is the option that had the most flexibility for me. Thanks for posting this solution. – Dale Moore Mar 25 '22 at 15:44
2
UPDATE messages SET 
 status=10 
WHERE ID in (SELECT TOP (10) Id FROM Table WHERE status=0 ORDER BY priority DESC);
dotjoe
  • 26,242
  • 5
  • 63
  • 77
0

As stated in comments below, you can use also the SET ROWCOUNT clause, but just for SQL Server 2014 and older.

SET ROWCOUNT 10

UPDATE messages
SET status = 10 
WHERE status = 0 

SET ROWCOUNT 0

More info: http://msdn.microsoft.com/en-us/library/ms188774.aspx

Or with a temp table

DECLARE @t TABLE (id INT)
INSERT @t (id)
SELECT TOP 10 id
FROM messages
WHERE status = 0
ORDER BY priority DESC

UPDATE messages
SET status = 10
WHERE id IN (SELECT id FROM @t)
Jhonny D. Cano -Leftware-
  • 17,663
  • 14
  • 81
  • 103
  • 5
    For those reading this far back.. (you never know.) SET ROWCOUNT is facing deprecation http://msdn.microsoft.com/en-us/library/ms188774.aspx "Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server." - it's good up to SQL Server 2014 though at least. – Tabloo Quijico Jun 11 '14 at 11:26
  • While `rowcount` works well if you want an arbitrary 10 rows, you can't specify `order by` to decide exactly which 10. Your example with a temp table works, but it relies on an id column. – Ed Avis Aug 31 '17 at 10:03