Updating Sequential values

Question

I have a table with ID and sequence # (SEQ) starting number. I want to update SQ number for each ID based on the SEQ

My table like this

ID       SQ    SEQ
---      --    ---
100      0      11
100      0      11
100      0      11
200      0      13
200      0      13
200      0      13
200      0      13

I want to get result table like this:

ID     | SQ | SEQ

100      11      11
100      12      11
100      13      11
200      13      13
200      14      13
200      15      13
200      16      13

How can I do this using TSQL?

Victor Hugo Terceros · Accepted Answer · 2017-10-02 17:00:29Z

1

Try this

select id,(ROW_NUMBER() over(partition by id order by id) + seq - 1) as sqn,seq from @tbl

Complete sample code with test data

declare @tbl table(id int, sq int, seq int)
insert into @tbl
select 100,0,11 union all
select 100,0,11 union all
select 100,0,11 union all
select 200,0,13 union all
select 200,0,13 union all
select 200,0,13 union all
select 200,0,13 

select id,(ROW_NUMBER() over(partition by id order by id) + seq - 1) as sqn,seq from @tbl

Result

id  sqn seq
100 11  11
100 12  11
100 13  11
200 13  13
200 14  13
200 15  13
200 16  13

answered Oct 2, 2017 at 17:00

Victor Hugo Terceros

3,1893 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fabiano Carvalho · Accepted Answer · 2017-10-02 16:57:19Z

0

try it's.

SELECT 
ID,ROW_NUMBER() OVER(ORDER BY ID ) AS SQ,SEQ
FROM YOUR_TABLE

And for update, use it's

 update a set sq = rn
    ID,ROW_NUMBER() OVER(ORDER BY ID ) AS rn,SEQ
    FROM YOUR_TABLE) as a

answered Oct 2, 2017 at 16:57

Fabiano Carvalho

5121 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

Updating Sequential values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related