SQL 如何通过 Row_Number 为每行使用不同的值更新列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13884011/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update a column via Row_Number with a different value for each row?
提问by Victor
I have this table right now
我现在有这张桌子
CREATE TABLE [dbo].[DatosLegales](
[IdCliente] [int] NOT NULL,
[IdDatoLegal] [int] NULL,
[Nombre] [varchar](max) NULL,
[RFC] [varchar](13) NULL,
[CURP] [varchar](20) NULL,
[IMSS] [varchar](20) NULL,
[Calle] [varchar](100) NULL,
[Numero] [varchar](10) NULL,
[Colonia] [varchar](100) NULL,
[Pais] [varchar](50) NULL,
[Estado] [varchar](50) NULL,
[Ciudad] [varchar](50) NULL,
[CodigoPostal] [varchar](10) NULL,
[Telefono] [varchar](13) NULL,
[TipoEmpresa] [varchar](20) NULL,
[Tipo] [varchar](20) NULL,
CONSTRAINT [PK_DatosLegales] PRIMARY KEY CLUSTERED
(
[IdCliente] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
I need to update the IdDatoLegal Column. Right now I have 80 rows on that table, so I need to update each row with the numbers 1, 2, 3... 79, 80.
我需要更新 IdDatoLegal 列。现在我在那个表上有 80 行，所以我需要用数字 1、2、3...79、80 更新每一行。
I have tried simple queries to stored procedures with no succeed at all.
我尝试过对存储过程的简单查询，但根本没有成功。
I have this stores procedure right now:
我现在有这个存储程序：
ALTER PROCEDURE dbo.ActualizarDatosLegales
@RowCount int
AS
DECLARE @Inicio int
SET @Inicio = 0
WHILE @Inicio < @@RowCount
SET @Inicio += 1;
BEGIN
UPDATE DatosLegales SET IdDatoLegal = @Inicio WHERE (SELECT ROW_NUMBER() OVER (ORDER BY IdCliente) AS RowNum FROM DatosLegales) = @Inicio;
END
It returns this message when I run it
当我运行它时它会返回此消息
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I guess that's because in the subquery (SELECT ROW_NUMBER() OVER (ORDER BY IdCliente) AS RowNum FROM DatosLegales) it returns 80 rows where it should only return one (but each time it should be a diferent number.
我猜这是因为在子查询 (SELECT ROW_NUMBER() OVER (ORDER BY IdCliente) AS RowNum FROM DatosLegales) 中它返回 80 行，其中它应该只返回一个（但每次它应该是一个不同的数字。
Do you know what do I have to add to the subquery to make it work? and above all, Is the loop and the rest of the procedure right?
您知道我必须向子查询添加什么才能使其工作吗？最重要的是，循环和程序的其余部分是否正确？
thanks in advance
提前致谢
回答by Martin Smith
You can update all the rows in one statement using a CTE as below.
您可以使用 CTE 更新一个语句中的所有行，如下所示。
;WITH T
AS (SELECT IdDatoLegal,
Row_number() OVER (ORDER BY IdCliente ) AS RN
FROM dbo.DatosLegales)
UPDATE T
SET IdDatoLegal = RN
回答by Kaf
UPDATE D
SET IdDatoLegal = RN
FROM DatosLegales D JOIN
(
SELECT IdCliente, Row_number() OVER (ORDER BY IdCliente) AS RN
FROM DatosLegales
) Temp
ON D.IdCliente = Temp.IdCliente
