phpmydev phpmydev - 2 months ago 7
MySQL Question

mysql update table set column3 where table1.column1 like concat ('%',table2.column2,'%')

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,'%')
)


here is a sample of col1 :
"455 N Cherokee St: Muskogee, OK 74403"
without the quotes

I hope I have given enough data to elicit an answer or two - thank you!

Answer

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.

Comments