How to update and order by using ms sql

Question

Hi,

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?

thanks!

Answer 1

You can do a subquery where you first get the IDs of the top 10 order by priority and the update the once 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);

Answer 2

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

Answer 3

You can use also the SET ROWCOUNT clase

SET ROWCOUNT 10

UPDATE messages
SET status = 10 
WHERE status=0 

SET ROWCOUNT 0

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)

Answer 4

UPDATE messages SET 
 status=10 
WHERE ID in (SELECT TOP (10) Id FROM Table WHERE status=0 ORDER BY priority DESC);

Answer 5

UPDATE messages SET status=10 WHERE status=0 ORDER BY priority DESC LIMIT 10;

Ai une clause ORDER BY est utilisée (disponible depuis MySQL version 4.0.0), les lignes seront modifiées selon cet ordre. Ce n'est vraiment utile qu'en conjonction avec LIMIT. plus>>

O.Hammami