我想用一个递增的数字更新我的列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
但由于联合,这也不起作用。
如何使用SQL Server2008 R2中的ROW_NUMBER()
函数更新列?
发布于 2012-12-01 00:28:58
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
发布于 2012-12-01 05:48:35
再多一个选择
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
发布于 2014-02-04 12:43:50
DECLARE @id INT
SET @id = 0
UPDATE DESTINATAIRE_TEMP
SET @id = CODE_DEST = @id + 1
GO
尝尝这个
https://stackoverflow.com/questions/13648898
复制相似问题