如何使用ms sql进行更新和排序 [英] How to update and order by using ms sql

问题描述

理想情况下我想这样做:

Ideally I want to do this:

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

英语:我想从数据库中获取前 10 条可用的 (status=0) 消息并锁定它们 (status=10).优先接收优先级高的消息.

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.

不幸的是，MS SQL 不允许在更新中使用 order by 子句.

unfortunately MS SQL doesn't allow an order by clause in the update.

无论如何如何规避?

推荐答案

您可以执行子查询，首先获取按优先级排序的前 10 个 ID，然后更新该子查询中的 ID:

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);

这篇关于如何使用ms sql进行更新和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！