CODEWITHSUNDEEP
sql-serversql-server-2008
Pabs
Pabs

Reputation: 11

Update an ID column using ROW_NUMBER

I am looking to update some ID fields which are currently NULL with the MAX(ID)+1 however I am having some issues pulling the correct syntax together, I was trying to use something like: 

UPDATE table
SET ID = (SELECT MAX(SELECT (ROW_NUMBER() OVER (order by ID) as rownumber 
                     from table))+1  from table)
where ID is NULL

however this clearly doesn't work, can anyone suggest what should done here?

The ID column does not auto increment and gets its value from another table which does auto increment its ID column, the reason the NULL have came in is due to some issues with the file which was being imported and the file needing to be directly imported into the second table.

Any help would be gratefully received

Upvotes: 1

Views: 1880

Answers (2)

Enes Okullu
Enes Okullu

Reputation: 340

If you have creationTime in your table you can easily use this;

You can set your starting number, and choose certain part of your table.

 DECLARE @startNumber INT = 1 -- Your start
    DECLARE @endNumber INT = (SELECT Count(Id) FROM YourTableName)

    WHILE (@startNumber <= @endNumber)
    BEGIN
    UPDATE YourTableName
    SET Id = @startNumber 
    WHERE CreationTime = 
(
SELECT 
CreationTime 
FROM 
( 
SELECT Row_Number() OVER (ORDER BY CreationTime ASC) AS RowNum, CreationTime FROM YourTableName
) t2 WHERE RowNum = @startNumber 
)

    SET @startNumber  = @startNumber  + 1
    END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If I understand correctly, you want to set the NULL values of id to incrementing values starting at the max of id plus 1. Try this:

with toupdate as (
      select t.*,
             (select max(id) from table t) as maxid,
             row_number() over (partition by id order by id) as seqnum
      from table t
     )
update toupdate
    set id = maxid + seqnum
    where id is null;

Upvotes: 1

Related Questions