SQL UPDATE TOP 与 ORDER BY? [英] SQL UPDATE TOP with ORDER BY?

问题描述

我有以下查询:

UPDATE TOP (@MaxRecords) Messages 
SET    status = 'P' 
OUTPUT inserted.* 
FROM   Messages 
where Status = 'N'
and InsertDate >= GETDATE()

在消息表中有优先级列，我想首先选择高优先级的消息.所以我需要一个 ORDER BY.但是我不需要在更新运行之前对输出进行排序而是对数据进行排序.

In the Messages table there is priority column and I want to select high priority messages first. So I need an ORDER BY. But I do not need to have sorted output but sorted data before update runs.

据我所知，不可能在 UPDATE 语句中添加 ORDER BY.还有其他想法吗?

As far as I know it's not possible to add ORDER BY to UPDATE statement. Any other ideas?

推荐答案

您可以为此使用公共表表达式:

you can use common table expression for this:

;with cte as (
   select top (@MaxRecords)
       status
   from Messages 
   where Status = 'N' and InsertDate >= getdate()
   order by ...
)
update cte set
    status = 'P'
output inserted.*

这个使用了这样一个事实，即在 SQL Server 中可以更新 cte，如可更新视图.

This one uses the fact that in SQL Server it's possible to update cte, like updatable view.

这篇关于SQL UPDATE TOP 与 ORDER BY?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！