Comma separated columns in MySQL-CodePudding

Let's say I have these MySQL tables:

USERS:

user-ID	Name
1	Saoirse
2	Gal
3	Margaret

PAYMENTS

pay-ID	user-IDs	Names
1		Margaret
2		Saoirse, Gal, Margaret
3		Gal, Saoirse

What I need is to update through MySQL the column user-IDs of the payments table with a comma separated user-ID of each user, searching by the name. This should be the result:

pay-ID	user-IDs	Names
1	3	Margaret
2	1, 2, 3	Saoirse, Gal, Margaret
3	2, 1	Gal, Saoirse

Does anyone have a clue?

Thanks a million!

CodePudding user response：

You should seriously move away from storing CSV data like this. It goes against how the database wants you to persist your data. Here is the PAYMENTS table, refactored to be normalized:

payID | userID | Name
1     |        | Margaret
2     |        | Saoirse
2     |        | Gal
2     |        | Margaret
3     |        | Gal
3     |        | Saoirse

Now if you need to update this table to bring in the user IDs, you may do a simple update join:

UPDATE PAYMENT p
INNER JOIN USERS u ON u.Name = p.Name
SET userID = u.userID;

Note that more typically you would always have the primary and foreign key IDs persisted already in the table, and rather you would be bringing in the names.

CodePudding user response：

You can use FIND_IN_SET to accomplish the task, even though I agree that you should normalize your data.

Try:

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 ;

Demo

If Names columns have spaces you can change the join condition to FIND_IN_SET(u.name,replace(p1.names,' ','')) or better run an update first replacing the spaces , because find_in_set has low performance.

Check the Demo