I saw recently (can't find it now) this syntax:
... LIKE CONCAT('%',col1,'%')
It is working for Selects but for update, it affects 0 rows
this is my query:
update locations set email = (
select col2 from vendoremail
where locations.city LIKE CONCAT('%',col1,'%')
AND locations.zip LIKE CONCAT('%',col1,'%')
You have it backwards. You want to put the city and zip into the pattern.
update locations set email = ( select col2 from vendoremail where col1 LIKE CONCAT('%', locations.city, '%', locations.zip, '%') )
However, this may not always work properly. If you have two vendors in the same city+zip, the subquery will return 2 emails, but when you use a subquery as a value it has to return only 1 row. You can add
LIMIT 1 to the subquery to prevent an error when this happens. But it will be selecting one of the vendors unpredictably -- maybe you should come up with a more reliable way to match the tables.