Como atualizar e ordenar usando ms sql

Idealmente eu quero fazer isso:

UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC; 

Em inglês: Eu quero colocar as 10 mensagens mais disponíveis (status = 0) do database e bloqueá-las (status = 10). Uma mensagem com prioridade mais alta deve ser obtida primeiro.

infelizmente o MS SQL não permite uma cláusula order by na atualização.

De qualquer forma, como contornar isso?

Você pode fazer uma subconsulta na qual primeiro obtém os IDs dos 10 principais ordenados por prioridade e, em seguida, atualiza os que estão nessa subconsulta:

 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 

Eu tenho que oferecer isso como uma abordagem melhor – você nem sempre tem o luxo de um campo de identidade:

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

Você também pode tornar a subconsulta tão complicada quanto você quiser – unindo várias tabelas, etc …

Por que isso é melhor? Ele não depende da presença de um campo de identidade (ou de qualquer outra coluna exclusiva) na tabela de messages . Ele pode ser usado para atualizar as N linhas superiores de qualquer tabela, mesmo se essa tabela não tiver nenhuma chave exclusiva.

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

Conforme mencionado nos comentários abaixo, você também pode usar a cláusula SET ROWCOUNT, mas apenas para o SQL Server 2014 e versões anteriores.

 SET ROWCOUNT 10 UPDATE messages SET status = 10 WHERE status = 0 SET ROWCOUNT 0 

Mais informações: http://msdn.microsoft.com/pt-br/library/ms188774.aspx

Ou com uma mesa temporária

 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)