如何使用 ms sql 进行更新和排序

How to update and order by using ms sql(如何使用 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.

无论如何如何规避这个?

Anyway how to circumvent this?

推荐答案

您可以进行子查询，首先获取按优先级排序的前 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 进行更新和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！