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 |