John K. John K. - 2 years ago 108
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 [email protected]
4567 [email protected]
2222
1111 [email protected]

-------------------------------------------------------------
| TABLE MARKET |
-------------------------------------------------------------
| ID | Emails | customersTemp|
-------------------------------------------------------------
|1 | [email protected], [email protected], [email protected] | |
|2 | [email protected], [email protected], [email protected] | |
|3 | [email protected] | |
|4 | [email protected] | |
-------------------------------------------------------------


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

Answer Source

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,'[email protected]'),
(4567,'[email protected]'),
(2222,''), 
(1111,'[email protected]'),
(5454,'[email protected]');

-- drop table market;
create table market
(   Emails varchar(100),
    customersTemp varchar(100)
);
insert market(Emails,customersTemp) values
('[email protected]',''),
('[email protected]',''),
('[email protected]',''),
('[email protected]','');

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 |
+---------------+---------------+
| [email protected] | 1234,5454     |
| [email protected]   | 4567          |
| [email protected] |               |
| [email protected] |               |
+---------------+---------------+

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download