CODEWITHSUNDEEP
sqlsql-server
George Daniel
George Daniel

Reputation: 590

Update Column With Incrementing Value

I currently have a table and let's say it has ~100 rows of data. One of the columns, "UserName", is currently null in 88 rows. The 12 rows with data looks like:

User1
User2
User3
User4
User5
User6
etc

I want to fill the rest of the 88 rows with User13, User14, etc... How do I update the rows to do this with the incrementing values?

Upvotes: 0

Views: 76

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would recommend an updatable CTE. This is a bit tricky because you need to continue the numbering, but I think this works:

with toupdate as (
      select t.*,
             row_number() over (order by (select null)) as seqnum,
             max(convert(int, stuff(username, 1, 4, ''))) over () as maxnum
      from t
     )
update toupdate
    set username = 'User' + convert(varchar(255), seqnum + maxnum)
    where username is null;

If you don't remind updating all the rows, you can use:

with toupdate as (
      select t.*,
             row_number() over (order by case when username is not null then convert(int, stuff(username, 1, 4, '')) else 999999 end) as seqnum
      from t
     )
update toupdate
    set username = 'User' + convert(varchar(255), seqnum);

This should keep the existing ordering, but if there are gaps then this would eliminate them.

Upvotes: 4

Ruslan Tolkachev
Ruslan Tolkachev

Reputation: 644

Would something like this work:

;WITH UserTable
AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY [user] DESC) as [row], 
           [UserName] as [user]
    FROM MyUserTable
) 
UPDATE u
SET 'User' + CAST(row AS varchar(10)) 
FROM UserTable u
WHERE u.[user] IS NULL

Upvotes: 2

Related Questions