Filling the ID column of a table NOT using a cursor

Question

Tables have been created and used without and ID column, but ID column is now needed. (classic)

I heard everything could be done without cursors. I just need every row to contain a different int value so I was looking for some kind of row number function :

How do I use ROW_NUMBER()?

I can't tell exactly how to use it even with these exemples.

UPDATE [TableA]
SET [id] = (select ROW_NUMBER() over (order by id) from [TableA])

Subquery returned more than 1 value.

So... yes of course it return more than one value. Then how to mix both update and row number to get that column filled ?

PS. I don't need a precise order, just unique values. I also wonder if ROW_NUMBER() is appropriate in this situation...

Answer 1

You can use a CTE for the update

Example

Declare @TableA table (ID int,SomeCol varchar(50))
Insert Into @TableA values
 (null,'Dog')
,(null,'Cat')
,(null,'Monkey')

;with cte as ( 
      Select *
            ,RN = Row_Number() over(Order by (Select null))
      From  @TableA
)
Update cte set ID=RN

Select * from @TableA

Updated Table

ID  SomeCol
1   Dog
2   Cat
3   Monkey