2

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...

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
Antoine Pelletier
  • 3,164
  • 3
  • 40
  • 62
  • You need a CTE or a SubQuery to use the Window Function – Ilyes Dec 13 '18 at 18:56
  • 1
    To avoid another classic issue, use a `BIGINT`. I've been through three of those debacles, trying to track down EVERY instance where the `ID` field is referenced, under pressure in the middle of the night when the `ID` hits 2,147,483,648. I know nobody thinks it'll _ever_ get that big. Until it does. – Eric Brandt Dec 13 '18 at 19:08

2 Answers2

2

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
1

You can use a subquery too as

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

UPDATE T1
SET T1.ID = T2.RN
FROM @TableA T1 JOIN
     (
       SELECT ROW_NUMBER()OVER(ORDER BY (SELECT 1)) RN,
              *
       FROM @TableA
     ) T2
ON T1.SomeCol = T2.SomeCol;


Select * from @TableA
Ilyes
  • 14,640
  • 4
  • 29
  • 55