MySQL 8.0

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

create table my_table(
 id int,
 date date ,
 pro_name varchar(10),
 count_item int,
  s_n int);

insert into my_table(id,date,pro_name,count_item) values 
(1,'2024-01-27','PRO-001',10),(2,'2024-01-28','PRO-002',15),
(3,'2024-01-29','PRO-003',20),(4,'2024-01-30','PRO-004',40),
(5,'2024-01-22','PRO-005',30),(6,'2024-01-23','PRO-006',10),
(7,'2024-01-31','PRO-007',50),(8,'2023-12-25','PRO-008',70),
(9,'2023-12-26','PRO-009',30),(10,'2024-01-28','PRO-010',80);

Records: 10  Duplicates: 0  Warnings: 0


select * from my_table;


id
date
pro_name
count_item
s_n


1
2024-01-27
PRO-001
10
null

2
2024-01-28
PRO-002
15
null

3
2024-01-29
PRO-003
20
null

4
2024-01-30
PRO-004
40
null

5
2024-01-22
PRO-005
30
null

6
2024-01-23
PRO-006
10
null

7
2024-01-31
PRO-007
50
null

8
2023-12-25
PRO-008
70
null

9
2023-12-26
PRO-009
30
null

10
2024-01-28
PRO-010
80
null


with To_Update as 
(
   select id,
          date,
          pro_name,
          count_item,
          row_number() over( order by date asc,id asc ) as s_n
   from my_table
)   
update  my_table m 
inner join To_Update t on m.id=t.id
set m.s_n=t.s_n; 

Rows matched: 10  Changed: 10  Warnings: 0


select * from my_table;


id
date
pro_name
count_item
s_n


1
2024-01-27
PRO-001
10
5

2
2024-01-28
PRO-002
15
6

3
2024-01-29
PRO-003
20
8

4
2024-01-30
PRO-004
40
9

5
2024-01-22
PRO-005
30
3

6
2024-01-23
PRO-006
10
4

7
2024-01-31
PRO-007
50
10

8
2023-12-25
PRO-008
70
1

9
2023-12-26
PRO-009
30
2

10
2024-01-28
PRO-010
80
7

id	date	pro_name	count_item	s_n
1	2024-01-27	PRO-001	10	null
2	2024-01-28	PRO-002	15	null
3	2024-01-29	PRO-003	20	null
4	2024-01-30	PRO-004	40	null
5	2024-01-22	PRO-005	30	null
6	2024-01-23	PRO-006	10	null
7	2024-01-31	PRO-007	50	null
8	2023-12-25	PRO-008	70	null
9	2023-12-26	PRO-009	30	null
10	2024-01-28	PRO-010	80	null