SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create Table table_name (ID int, SEQ_NO varchar(10));
/* generate data*/
with cte as
  (
   select 2 as id, 1 as seq_no
   union all
   select 2, seq_no+1
   from cte where seq_no+1<11
  )
insert into table_name select id, format(seq_no,'00') from cte union all
select id, format(seq_no,'00') from cte union all
select id, format(seq_no,'00') from cte union all
select id, format(seq_no,'00') from cte ;
40 rows affected

delete from table_name where seq_no='04'
4 rows affected

WITH CTE AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, SEQ_NO ORDER BY SEQ_NO) rn 
  FROM table_name
)
SELECT ID, SEQ_NO,
       CASE 
         WHEN rn>1 THEN
          CONCAT(CHAR(rn+63), CHAR(rn+63), SEQ_NO) 
         ELSE SEQ_NO
       END AS new_seq
FROM CTE 
WHERE rn <= 27
ORDER BY ID, new_seq


ID
SEQ_NO
new_seq


2
01
01

2
02
02

2
03
03

2
05
05

2
06
06

2
07
07

2
08
08

2
09
09

2
10
10

2
01
AA01

2
02
AA02

2
03
AA03

2
05
AA05

2
06
AA06

2
07
AA07

2
08
AA08

2
09
AA09

2
10
AA10

2
01
BB01

2
02
BB02

2
03
BB03

2
05
BB05

2
06
BB06

2
07
BB07

2
08
BB08

2
09
BB09

2
10
BB10

2
01
CC01

2
02
CC02

2
03
CC03

2
05
CC05

2
06
CC06

2
07
CC07

2
08
CC08

2
09
CC09

2
10
CC10


WITH CTE AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, SEQ_NO ORDER BY SEQ_NO) rn 
  FROM table_name
)
UPDATE CTE SET SEQ_NO = CONCAT(CHAR(rn+63), CHAR(rn+63), SEQ_NO) 
        
WHERE rn > 1 AND rn <= 27
27 rows affected

SELECT * FROM table_name
ORDER BY ID, SEQ_NO


ID
SEQ_NO


2
01

2
02

2
03

2
05

2
06

2
07

2
08

2
09

2
10

2
AA01

2
AA02

2
AA03

2
AA05

2
AA06

2
AA07

2
AA08

2
AA09

2
AA10

2
BB01

2
BB02

2
BB03

2
BB05

2
BB06

2
BB07

2
BB08

2
BB09

2
BB10

2
CC01

2
CC02

2
CC03

2
CC05

2
CC06

2
CC07

2
CC08

2
CC09

2
CC10

ID	SEQ_NO	new_seq
2	01	01
2	02	02
2	03	03
2	05	05
2	06	06
2	07	07
2	08	08
2	09	09
2	10	10
2	01	AA01
2	02	AA02
2	03	AA03
2	05	AA05
2	06	AA06
2	07	AA07
2	08	AA08
2	09	AA09
2	10	AA10
2	01	BB01
2	02	BB02
2	03	BB03
2	05	BB05
2	06	BB06
2	07	BB07
2	08	BB08
2	09	BB09
2	10	BB10
2	01	CC01
2	02	CC02
2	03	CC03
2	05	CC05
2	06	CC06
2	07	CC07
2	08	CC08
2	09	CC09
2	10	CC10