Ну зачем же сразу цикл и курсор

Если я правильно понял идею, то замена первого символа должна идет строго по порядку


create table #table1(num int not null, code char(8 ) not null)
insert #table1 values(1, 'A0000001')
insert #table1 values(2, 'A0000001')
insert #table1 values(3, 'A0000001')
insert #table1 values(4, 'A0000002')
insert #table1 values(5, 'A0000002')
insert #table1 values(6, 'A0000003')

/*
select *, CHAR(ASCII(LEFT(a.code,1))+(a.num-a.a_num))+SUBSTRING(a.code, 2, 7) as code
from
(select b.num, b.code, min(a.num) as a_num
from #table1 a
inner join #table1 b on a.num < b.num and b.code = a.code
group by b.num, b.code
) AS a
*/

UPDATE #table1
SET code = b.code
from #table1 a
inner join
(select a.num, CHAR(ASCII(LEFT(a.code,1))+(a.num-a.a_num))+SUBSTRING(a.code, 2, 7) as code
from
(select b.num, b.code, min(a.num) as a_num
from #table1 a
inner join #table1 b on a.num < b.num and b.code = a.code
group by b.num, b.code
) AS a
) AS b ON a.num = b.num

select * from #table1

drop table #table1


ЗЫ
При пропуске значеня в Num предложенный код будет также делать пропуски, т.е

1,A0000001 -->> 1,A0000001
3,A0000001 -->> 3,С0000001

А так будет работать даже с пропусками в Num


create table #table1(num int not null, code char(8 ) not null)
insert #table1 values(1, 'A0000001')
insert #table1 values(3, 'A0000001')
insert #table1 values(5, 'A0000001')
insert #table1 values(6, 'A0000003')

UPDATE #table1
SET code = b.code
from #table1 a
inner join
(select a.num, CHAR(ASCII(LEFT(a.code,1))+a.a_num)+SUBSTRING(a.code, 2, 7) as code
from
(select b.num, b.code, COUNT(a.num) as a_num
from #table1 a
inner join #table1 b on a.num < b.num and b.code = a.code
group by b.num, b.code
) AS a
) AS b ON a.num = b.num

select * from #table1

drop table #table1