Update table using a complex select.
Ok, I'm confused how to pull this off. Let me describe what I have going on...
Actual database is MySQL 5.0.33
I've got this table(calls). Let's just say there are only two columns; number, location.
And I've got this other table(locations), two columns; number_pattern, name.
Of course in the real world I have hundreds of thousands of locations with several columns and I have millions of call records where I want to update the table and insert the "locations.name" into the location column of the calls table.
This code here does a nice job of finding the location that matches most closely.
//WORKING - one row at a time
SET @digits := "16125551212";
SELECT @digits, locations.name, FROM locations
WHERE locations.number_pattern in (
ORDER BY LENGTH(locations.number_pattern) DESC LIMIT 1
So where I'm lost is how to write my update query to ask SQL to loop over all the records WHERE call.location = 'NULL' and do my little query to update things.