Don't forget to read the Warning at the bottom as to why you should NEVER save your data like this.
You can test this on a backup copy. I wouldn't run it against your main tables. Akin to someone saying: "Here, try this delete command, I think it will work."
-- drop table customer;
create table customer
( ClientNumber int,
EMailAddress varchar(100)
);
insert customer (ClientNumber,EMailAddress) values
(1234,'john@john.com'),
(4567,'joe@joe.com'),
(2222,''),
(1111,'somone@someone.com'),
(5454,'john@john.com');
-- drop table market;
create table market
( Emails varchar(100),
customersTemp varchar(100)
);
insert market(Emails,customersTemp) values
('john@john.com',''),
('joe@joe.com',''),
('test@test.com',''),
('more@more.com','');
The Update statement:
UPDATE market
INNER JOIN
( SELECT c.EMailAddress as e,GROUP_CONCAT(c.ClientNumber ORDER BY c.ClientNumber) theList
FROM customer c
GROUP BY c.EMailAddress
) xDerived1
ON market.EMails = xDerived1.e
SET market.customersTemp = xDerived1.theList;
Results:
select * from market;
+---------------+---------------+
| Emails | customersTemp |
+---------------+---------------+
| john@john.com | 1234,5454 |
| joe@joe.com | 4567 |
| test@test.com | |
| more@more.com | |
+---------------+---------------+
Version2
drop table customer;
create table customer
( ClientNumber int,
EMailAddress varchar(100)
);
insert customer (ClientNumber,EMailAddress) values
(1234,'a@a.com'),
(4567,'b@b.com'),
(2222,''),
(1111,'d@d.com'),
(8484,'g@g.com');
-- select * from customer;
drop table market;
create table market
( id int auto_increment primary key,
Emails varchar(100),
customersTemp varchar(3000)
);
insert market(Emails,customersTemp) values
('a@a.com,b@b.com,c@c.com',''),
('a@a.com,b@b.com,g@g.com',''),
('e@e.com',''),
('f@f.com','');
-- select * from market;
drop table if exists marketHelper7;
create table marketHelper7
( -- btw this might be the kind of table
-- as an intersect/junction table that you
-- should have to begin with
-- and not have your CSV stuff
cid int not null,
mid int not null
);
insert marketHelper7 (cid,mid)
select c.ClientNumber,m.id as MarketId
from customer c
join market m
on find_in_set(c.EMailAddress,m.Emails)>0;
update market set customersTemp=''; -- do a reset
UPDATE market m
join
( SELECT mh.mid as i,GROUP_CONCAT(mh.cid ORDER BY mh.cid) theList
FROM marketHelper7 mh
GROUP BY mh.mid
) xDerived1
ON m.id = xDerived1.i
SET m.customersTemp = xDerived1.theList;
drop table marketHelper7;
.
select * from market;
+----+-------------------------+----------------+
| id | Emails | customersTemp |
+----+-------------------------+----------------+
| 1 | a@a.com,b@b.com,c@c.com | 1234,4567 |
| 2 | a@a.com,b@b.com,g@g.com | 1234,4567,8484 |
| 3 | e@e.com | |
| 4 | f@f.com | |
+----+-------------------------+----------------+
Version 2 above has the helper table.
Warning:
By the way, never save your data like this. It is insane, and the performance is awful. Please see my answer here on Junction Tables (many-to-many) (similar to association tables or "item has" tables a.k.a. One-to-Many). They are all the same concept that utilized Data Normalization best practices and fast indexes during queries. Plus you stay happier not fighting with your data constantly or wondering if you blow the buffer size with group_concat.
Note that group_concat() has flexibility for its separator choice, and the order by, baked inside the function call.
The maximum length for the output of group_concat is subject to the system variable group_concat_max_len which probably defaults to 1K but can be set to at least 4GB.
The Percona article on group_concat(), and the manual pages for group_concat() and find_in_set().
