I reached out here earlier to get some code for the following problem, and it's worked great, however I'm now using it on a table with about 3 Million records and it takes forever and eventually times out.
Here's what I have. I have a table with Cities and States and an empty Zip field. I'm using the statement below to populate the zip field based on the name of the city and state and a table that stores all my city, state, zip info:
UPDATE Z_MyTable S
STRAIGHT_JOIN CityStateZip_LatLong SA
ON S.State = SA.state
SET S.Zip = SA.zipcode
WHERE S.City LIKE CONCAT('%',SA.city,'%')
AND S.State = SA.state
Is there any way that I can maybe order the Z_MyTable within this query in order to make it run faster? I'm at a loss, but it just doesn't seem to work great with that many records...
Thanks for the help!