MySQL 5.7

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

CREATE TABLE mytable(
   id       VARCHAR(34) NOT NULL PRIMARY KEY
  ,room_id  INTEGER 
  ,seat_num VARCHAR(6)
);
INSERT INTO mytable(id,room_id,seat_num) VALUES ('1',1,NULL);
INSERT INTO mytable(id,room_id,seat_num) VALUES ('2',1,NULL);
INSERT INTO mytable(id,room_id,seat_num) VALUES ('3',2,NULL);
INSERT INTO mytable(id,room_id,seat_num) VALUES ('4',2,NULL);
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

Rows matched: 4  Changed: 4  Warnings: 0


select * from mytable


id
room_id
seat_num


1
1
1

2
1
2

3
2
1

4
2
2