Come aggiornare e ordinare usando ms sql

Idealmente voglio fare questo:

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

In inglese: voglio ottenere i primi 10 messaggi disponibili (status = 0) dal DB e bloccarli (stato = 10). Un messaggio con una priorità più alta dovrebbe essere ottenuto per primo.

sfortunatamente MS SQL non consente una clausola order by nell’aggiornamento.

Ad ogni modo come aggirare questo?

Puoi eseguire una sottoquery in cui ottieni per primi gli ID dei primi 10 ordinati per priorità, quindi aggiorna quelli che si trovano in quella sottoquery:

 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 

Devo offrire questo come approccio migliore: non sempre si ha il lusso di un campo di identity framework:

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

Puoi anche rendere la sotto-query più complicata che vuoi: unire più tabelle, ecc …

Perché è meglio? Non si basa sulla presenza di un campo identity framework (o di qualsiasi altra colonna univoca) nella tabella dei messages . Può essere utilizzato per aggiornare le prime N righe da qualsiasi tabella, anche se quella tabella non ha alcuna chiave univoca.

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

Come indicato nei commenti seguenti, è ansible utilizzare anche la clausola SET ROWCOUNT, ma solo per SQL Server 2014 e precedenti.

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

Maggiori informazioni: http://msdn.microsoft.com/en-us/library/ms188774.aspx

O con una tabella temporanea

 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)