how to update column with row number in group by result in MySQL

Sample Data:

id  |  room_id  |  seat_num
----------------------------------
1   |   1      |  null
2   |   1      |  null
3   |   2      |  null
4   |   2      |  null

Desire Data:

id  |  room_id  |  seat_num
----------------------------------
1   |   1      |  1
2   |   1      |  2
3   |   2      |  1
4   |   2      |  2

how to write a sql to update the room seat number to serial num in MySQL 5.7? the room’s seat is from 2-20.

Answer

One option uses the update/join syntax. In MySQL 5.7, where window functions are not available, you can emulate row_number() with a correlated subquery (which is somehow safer than user variables):

update mytable t
inner join (
    select id, 
        (select count(*) from mytable t1 where t1.room_id = t.room_id and t1.id <= t.id) rn
    from mytable t
) t1 on t1.id = t.id
set t.seat_num = t1.rn

Demo on DB Fiddle:

id | room_id | seat_num
:- | ------: | :-------
1  |       1 | 1       
2  |       1 | 2       
3  |       2 | 1       
4  |       2 | 2