Articles → SQL SERVER → Update Column With Row Number In SQL Server

Update Column With Row Number In SQL Server

This article describes how to update a column with a row number.

Table And Data

First, create a table with the following schema: -

Picture showing the schema of the sample table

Click to Enlarge

Enter data into the table with the following script: -

Declare @i as Int 
Set 
  @i = 1 While @i <> 50 Begin Insert into Employee(Employee_Name) 
Values 
  (
    'Employee ' + cast(
      @i as varchar(2)
    )
  ) 
Set 
  @i = @i + 1 End

Once you execute the above code, the following data is inserted into the table.

Picture showing the record inserted into the sample table

Click to Enlarge

Updating Row Number

In the preceding image, the value of the "Employee_Name" column is updated but the "count" column is not updated. For updating the count, you can use the following script: -

UPDATE 
  y 
SET 
  y.[Count] = x.[Row_Number] 
FROM 
  Employee y 
  Inner Join (
    SELECT 
      Id, 
      ROW_NUMBER() OVER (
        ORDER BY 
          [Id]
      ) AS [Row_Number] 
    FROM 
      Employee
  ) x on y.Id = x.Id

Output

Now run the select query again. You will get the following output.

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Friday, November 27, 2015

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**