How to update a column via Row_Number with a different value for each row?

Question

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.

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.

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

Answer 1

You can update all the rows in one statement using a CTE as below.

;WITH T
     AS (SELECT IdDatoLegal,
                Row_number() OVER (ORDER BY IdCliente ) AS RN
         FROM   dbo.DatosLegales)
UPDATE T
SET    IdDatoLegal = RN