John K. John K. - 5 months ago 9
SQL Question

MySQL populating field based on another table using LIKE match

I know it's not the cleanest code to date, but I can't figure out why I can't get this one to work.

I'm looking to populate the field m.customersTemp with Customer Numbers from field c.ClientNumber. But only when a LIKE match from c.EmailAddress is found m.Emails... m.Emails is a field with a list of e-mails. Code Below.

UPDATE market m, customer c
SET m.customersTemp = CONCAT(m.customersTemp, c.ClientNumber)
WHERE m.Emails LIKE CONCAT('%', TRIM(c.EMailAddress), '%')
AND TRIM(c.EMailAddress)<>''


The result in field m.customersTemp only displays one value (customer number)... and I know there are many matches.

TABLE CUSTOMER
ClientNumber | EMailAddress
1234 a@a.com
4567 b@b.com
2222
1111 d@d.com

-------------------------------------------------------------
| TABLE MARKET |
-------------------------------------------------------------
| ID | Emails | customersTemp|
-------------------------------------------------------------
|1 | a@a.com, b@b.com, c@c.com | |
|2 | a@a.com, b@b.com, g@g.com | |
|3 | e@e.com | |
|4 | f@f.com | |
-------------------------------------------------------------


Result in customersTemp at ID 1 and 2 is only 1 ClientNumber. 4567

Answer

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 |               |
+---------------+---------------+

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 main Manual Page.