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?
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);
WITH q AS
(
SELECT TOP 10 *
FROM messages
WHERE status = 0
ORDER BY
priority DESC
)
UPDATE q
SET status = 10
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.
UPDATE messages SET
status=10
WHERE ID in (SELECT TOP (10) Id FROM Table WHERE status=0 ORDER BY priority DESC);
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)
