Re sequence lines

1

I have a table with a sequence number that is currently sequenced by 10. I want to change it so it is sequenced by 2.

Table name: bmengchg

Chg_no------ item_no ------- seq_no
1234 --------ABC ------------- 10
1234 ----------ABC----------------20
1234-----------ABC----------------30

Want to change seq_no to:

2
4
6

2

What controls the sequence? Identity? Code? Trigger?
What is your table definition like?

3

Sorry forgot to add that.

comp_item_no

4

Chg_no------ item_no ------- seq_no-----comp_item_no
1234 --------ABC ------------- 10-------------XYZ
1234 ----------ABC----------------20---------TTT
1234-----------ABC----------------30---------ADF

5

use tempdb

drop table #test

create table #test (Chg_no int, item_no varchar(255), seq_no int)

insert into #test values
(1234,'ABC',10)
,(1234,'ABC',30)
,(1234,'ABC',20)

--Your current table
select * from #test

--increment by 2
declare @num int

set @num = (
select COUNT(*)
from #test
--if you have a where codition apply here
)

update main
set main.seq_no = main2.Rt
from #test main
inner join (
Select
Chg_no
,item_no
,seq_no
,RT
from ( select
row_number() over (order by seq_no ) rn ,*
from #test
) A
Left join (
Select
row_number() over (order by RT ) rn,
RT
from (
select top (@num) *
from (
select ROW_NUMBER() over (order by (select Null))as RT
from sys.all_columns
) main where right(RT,1)not in (1,3,5,7,9)
) numb
) b on a.rn = b.rn
) main2
on main.Chg_no = main2.Chg_no and main.item_no = main2.item_no and main.seq_no = main2.seq_no

--Your finished table
select * from #test