MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users(
user_id INT,
name varchar(30) );

insert into users values
(1,'Saoirse'),
(2,'Gal'),
(3,'Margaret');

CREATE TABLE payments(
pay_id int, 
user_id varchar(10),
names varchar(255) );

insert into payments values
(1,null,'Margaret'),
(2,null,'Saoirse,Gal,Margaret'),
(3,null,'Gal,Saoirse');



select pay_id,group_concat(u.user_id separator ',') as user_id
             from payments p1
             inner join users u on FIND_IN_SET(u.name,p1.names) 
             group by pay_id;


pay_id
user_id


1
3

2
1,2,3

3
1,2


update payments p 
inner join (
             select pay_id,group_concat(u.user_id separator ',') as user_id
             from payments p1
             inner join users u on FIND_IN_SET(u.name,p1.names) 
             group by pay_id
         ) as p2 on p.pay_id=p2.pay_id
set p.user_id=p2.user_id ;      
select * from payments;


pay_id
user_id
names


1
3
Margaret

2
1,2,3
Saoirse,Gal,Margaret

3
1,2
Gal,Saoirse