使用CTE和行号作为序列进行更新，或使用row_NUMBER()进行TSQL更新查询

Question

我有一个名为Site的表，其中有列Name、SiteId和Sequence。我想用行号填充Sequence字段。我尝试了以下查询，但它不会更新记录：

WITH RowNumbers AS 
(
    select   SiteId,
             RowNum = row_number() OVER ( order by SiteId )
    from     [Site] 
)
UPDATE  s
SET     s.[Sequence] = r.RowNum
FROM    [Site] as s INNER JOIN RowNumbers as r ON s.SiteId = r.Row

我做错了什么？

Answer 1

您可以直接更新CTE。。。

WITH RowNumbers AS 
(
    select   *,
             RowNum = row_number() OVER ( order by SiteId )
    from     [Site] 
)
UPDATE  RowNumbers
SET     [Sequence] = RowNum

这与可更新视图的工作方式相同。我添加了*以确保更新的字段通过，然后直接更新它。

Answer 2

您应该加入r.SiteID，而不是r.Row

WITH RowNumbers AS  
( 
    select   SiteId, 
             RowNum = row_number() OVER ( order by SiteId ) 
    from     [Site]  
) 
UPDATE  s 
SET     s.[Sequence] = r.RowNum 
FROM    [Site] as s INNER JOIN RowNumbers as r ON s.SiteId = r.SiteID