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');



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,replace(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