如何分配ROW_NUMBER（）的列？(How to assign ROW_NUMBER() to

看完这个问题 ，我有另一个类似的问题。 有一个简单的办法让这条SQL语句的工作？

update [Insurances] set [RowNo]=ROW_NUMBER() over (order by [RowNo])

我有一个RowNo我希望每当一条记录被删除，更新列。

Answer 1:

UPDATE t1 
SET t1.RowNo = s.RowNo  
FROM  [Insurances] t1
INNER JOIN
(
     SELECT Id, ROW_NUMBER() OVER( ORDER BY Somefield DESC) RowNo
     FROM Insurances
) s ON t1.Id = s.Id

Answer 2:

我将加入表子查询（不知道你是否能做到这一点在SQL Server）或创建一个视图并加入表，在更新：

CREATE VIEW InsurancesView AS
SELECT insuranceID, ROW_NUMBER() OVER ( ORDER BY RowNo ) AS newRowNo
  FROM Insurances;

（假设你有一个名为一个主键insuranceID ）

UPDATE Insurances AS i
 INNER JOIN InsurancesView AS iv
    ON i.insuranceID = iv.insuranceID
   SET i.RowNo = iv.newRowNo;

希望这可以帮助。

Answer 3:

WITH cte AS
(
SELECT Id, ROW_NUMBER() OVER(ORDER BY Name ) RowNo FROM lu_Domain
)
UPDATE dbo.lu_Domain
SET SortOrder = cte.RowNo
FROM dbo.lu_Domain d
INNER JOIN cte ON cte.id = d.Id

文章来源: How to assign ROW_NUMBER() to a column?