SQL Server:使用 ORDER BY 更新表 [英] SQL Server: UPDATE a table by using ORDER BY

问题描述

我想知道在更新表时是否可以使用 order by 子句.我正在更新表格并设置一个连续的数字，这就是更新顺序很重要的原因.使用下面的sql语句，我不用游标就可以解决它:

I would like to know if there is a way to use an order by clause when updating a table. I am updating a table and setting a consecutive number, that's why the order of the update is important. Using the following sql statement, I was able to solve it without using a cursor:

DECLARE @Number INT = 0

UPDATE Test
SET @Number = Number = @Number +1

现在我想做的是一个像这样的 order by 子句:

now what I'd like to to do is an order by clause like so:

 DECLARE @Number INT = 0

 UPDATE Test
 SET @Number = Number = @Number +1
 ORDER BY Test.Id DESC

我已阅读:如何更新和订购使用 ms sql 这个问题的解决方案不能解决排序问题——它们只是过滤应用更新的项目.

I've read: How to update and order by using ms sql The solutions to this question do not solve the ordering problem - they just filter the items on which the update is applied.

当心，马丁

推荐答案

没有

没有记录在案的 100% 支持的方式.有时用于计算运行总数的方法称为quirky update"这表明如果满足某些条件，它可能会按聚集索引的顺序更新，但据我所知，这完全依赖于经验观察而不是任何保证.

Not a documented 100% supported way. There is an approach sometimes used for calculating running totals called "quirky update" that suggests that it might update in order of clustered index if certain conditions are met but as far as I know this relies completely on empirical observation rather than any guarantee.

但是您使用的是哪个版本的 SQL Server?如果 SQL2005+，您可能可以使用 row_number 和 CTE(您可以更新 CTE)

But what version of SQL Server are you on? If SQL2005+ you might be able to do something with row_number and a CTE (You can update the CTE)

With cte As
(
SELECT id,Number,
ROW_NUMBER() OVER (ORDER BY id DESC) AS RN
FROM Test
)
UPDATE cte SET Number=RN

这篇关于SQL Server:使用 ORDER BY 更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！