CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Deepika Krishnamoorty
Deepika Krishnamoorty

Reputation: 23

Update Column using Row Number based on primary key column in SQL Server

I have table in below format

UserID  SeqID  RowNum
 1      8041     0          
 1      8045     0            
 1      8045     0
 2      6587     0
 2      5624     0

I want to update the RowNum column based on the userId column as follows

UserID  SeqID  RowNum
 1      8041     1          
 1      8045     2            
 1      8045     3
 2      6587     1
 2      5624     2

How to use row number concept to update the RowNum column?

Note: it is just sample data. So I can't update using hardcoding the UserId values. I have millions of records in this table.

Thanks in advance.

Upvotes: 1

Views: 1774

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

In SQL Server, you would use row_number() and a CTE:

with toupdate as (
      select t.*, row_number() over (partition by UserId order by SeqId) as seqnum
      from table t
     )
update toupdate
     set rownum = seqnum;

Upvotes: 3

Related Questions