How to update and order by using ms sql

reinier_71883 0
Viewed 116534

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?

sql-update sql-order-by tsql sql-server sql
5 Answers
WITH    q AS
        (
        SELECT  TOP 10 *
        FROM    messages
        WHERE   status = 0
        ORDER BY
                priority DESC
        )
UPDATE  q
SET     status = 10
user_551590

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);
Quaky_481320 
UPDATE messages SET 
 status=10 
WHERE ID in (SELECT TOP (10) Id FROM Table WHERE status=0 ORDER BY priority DESC);
Joe_408220

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)
user_768320
Related