Articles → SQL SERVER → Update column with row number in SQL server

Update column with row number in SQL server






Table and data





Click to Enlarge



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



Click to Enlarge


Updating row number




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





Click to Enlarge


Posted By  -  Karan Gupta
 
Posted On  -  Friday, November 27, 2015

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250