CODEWITHSUNDEEP
sqlsql-serversql-server-2012sequencealter-table
EkansDasnakE
EkansDasnakE

Reputation: 67

How to read data from table and assign sequence number?

I am trying to create add a column to a table but i'm stuck trying to figure out a way to assign sequence number to this column.

Here is what I have:

ID           C_Seq_No        D_Seq_No         Type
123              1             NULL            02
123              1             NULL            04
123              1             NULL            06
123              2             NULL            03
123              2             NULL            05
123              2             NULL            07

This is what I want in my table:

ID           C_Seq_No        D_Seq_No         Type
123              1             1               02
123              1             2               04
123              1             3               06
123              2             1               03
123              2             2               05
123              2             3               07

Thank you for your help!

Upvotes: 0

Views: 91

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You are looking for row_number():

select t.*,
       row_number() over (partition by id, c_seq_no order by type) as d_seq_no
from t;

If you actually want an update, you can use an updatable CTE:

with toupdate as (
      select t.*,
             row_number() over (partition by id, c_seq_no order by type) as new_d_seq_no
      from t
     )
update toupdate
    set d_seq_no = new_d_seq_no;

Upvotes: 4

Related Questions