SQL更新与ROW_NUMBER（）(SQL Update with row_number())

我想用一个递增的数字来更新我的专栏CODE_DEST。我有：

CODE_DEST   RS_NOM
null        qsdf
null        sdfqsdfqsdf
null        qsdfqsdf

我想更新为：

CODE_DEST   RS_NOM
1           qsdf
2           sdfqsdfqsdf
3           qsdfqsdf

我曾尝试这样的代码：

UPDATE DESTINATAIRE_TEMP
SET CODE_DEST = TheId 
FROM (SELECT  Row_Number()   OVER (ORDER BY [RS_NOM]) AS TheId FROM DESTINATAIRE_TEMP)

这并不是因为工作)

我也曾尝试：

WITH DESTINATAIRE_TEMP AS
  (
    SELECT 
    ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
    FROM DESTINATAIRE_TEMP
  )
UPDATE DESTINATAIRE_TEMP SET CODE_DEST=RN

但是，这也并不因为工会的工作。

如何更新使用列ROW_NUMBER()在SQL Server 2008 R2的功能？

Answer 1:

还有一个选项

UPDATE x
SET x.CODE_DEST = x.New_CODE_DEST
FROM (
      SELECT CODE_DEST, ROW_NUMBER() OVER (ORDER BY [RS_NOM]) AS New_CODE_DEST
      FROM DESTINATAIRE_TEMP
      ) x

Answer 2:

DECLARE @id INT 
SET @id = 0 
UPDATE DESTINATAIRE_TEMP
SET @id = CODE_DEST = @id + 1 
GO

试试这个

http://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/

Answer 3:

With UpdateData  As
(
SELECT RS_NOM,
ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
FROM DESTINATAIRE_TEMP
)
UPDATE DESTINATAIRE_TEMP SET CODE_DEST = RN
FROM DESTINATAIRE_TEMP
INNER JOIN UpdateData ON DESTINATAIRE_TEMP.RS_NOM = UpdateData.RS_NOM

Answer 4:

因为你命名CTE相同基础表和取得的CTE看起来好像它是一个递归CTE，因为它基本上是引用本身你的第二次尝试失败，主要。一个递归CTE必须有其需要使用的具体结构UNION ALL集合运算符。

相反，你可以只给热膨胀系数不同的名称，以及添加的目标列到它：

With SomeName As
(
SELECT 
CODE_DEST,
ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
FROM DESTINATAIRE_TEMP
)
UPDATE SomeName SET CODE_DEST=RN

Answer 5:

这是@Aleksandr费德林的答案添加一个WHERE子句的修改版本：

UPDATE x
SET x.CODE_DEST = x.New_CODE_DEST
FROM (
      SELECT CODE_DEST, ROW_NUMBER() OVER (ORDER BY [RS_NOM]) AS New_CODE_DEST
      FROM DESTINATAIRE_TEMP
      ) x
WHERE x.CODE_DEST <> x.New_CODE_DEST AND x.CODE_DEST IS NOT NULL

通过添加一个WHERE子句，我发现性能后续更新极大的改进。 SQL SERVER似乎即使该值已经存在更新的行，它需要的时间这样做，因此添加where子句令它跳过行，其中的价值并没有改变。我不得不说，我很惊讶它是如何快速可以运行我的查询。

免责声明：我不是DB专家，我使用PARTITION BY为我的条款，因此可能不会恰好是此查询相同的结果。对我来说，有问题的列是客户的支付顺序，因此值通常一旦设置不会改变。

另外，还要确保你有指标，特别是如果你对SELECT语句的WHERE子句。筛选的索引，因为我是根据付款状态滤波工作对我来说太棒了。

使用分区由我的查询

UPDATE  UpdateTarget
SET     PaidOrderIndex = New_PaidOrderIndex
FROM
(
    SELECT  PaidOrderIndex, SimpleMembershipUserName, ROW_NUMBER() OVER(PARTITION BY SimpleMembershipUserName ORDER BY OrderId) AS New_PaidOrderIndex
    FROM    [Order]
    WHERE   PaymentStatusTypeId in (2,3,6) and SimpleMembershipUserName is not null
) AS UpdateTarget

WHERE UpdateTarget.PaidOrderIndex <> UpdateTarget.New_PaidOrderIndex AND UpdateTarget.PaidOrderIndex IS NOT NULL

-- test to 'break' some of the rows, and then run the UPDATE again
update [order] set PaidOrderIndex = 2 where PaidOrderIndex=3

在 'IS NOT NULL'，如果列不可为空是不需要的部分。

当我说业绩增长呈块状我的意思是更新少量的行数时，它基本上是瞬间的。有了正确的指标，我能够做到这一点花了相同的时间量为“内在”查询本身并更新：

  SELECT  PaidOrderIndex, SimpleMembershipUserName, ROW_NUMBER() OVER(PARTITION BY SimpleMembershipUserName ORDER BY OrderId) AS New_PaidOrderIndex
    FROM    [Order]
    WHERE   PaymentStatusTypeId in (2,3,6) and SimpleMembershipUserName is not null

Answer 6:

简单易用的方式来更新光标

UPDATE Cursor
SET Cursor.CODE = Cursor.New_CODE
FROM (
  SELECT CODE, ROW_NUMBER() OVER (ORDER BY [CODE]) AS New_CODE
  FROM Table Where CODE BETWEEN 1000 AND 1999
  ) Cursor

Answer 7:

我这样做是为了我的情况和工作

WITH myUpdate (id, myRowNumber )
AS
( 
    SELECT id, ROW_NUMBER() over (order by ID) As myRowNumber
    FROM AspNetUsers
    WHERE  UserType='Customer' 
 )

update AspNetUsers set EmployeeCode = FORMAT(myRowNumber,'00000#') 
FROM myUpdate
    left join AspNetUsers u on u.Id=myUpdate.id