I have an
orders table on a database that contains geographical information about the city and state that the order is from. This includes latitude and longitude for doing haversine calculations.
I have a new geo-location database containing the latitude and longitude of said city states that differs slightly from my previous database. This introduces some odd behavior because when I run haversine calculations, mileage will not be calculated correctly due to the slight differences in latitude and longitude from the previous database.
What I need to do is update the latitude and longitude on the orders table to match what occurs in the geo-location database. This is based off the city and state that is on the order. I am trying to update my orders table like so:
update ProcurementPortal.orders as orders inner join ProcurementPortal.cities_extended as geo on orders.city = geo.city and orders.state = geo.state_code set orders.lat = geo.latitude, orders.lon = geo.longitude where orders.city = geo.city and orders.state = geo.state_code
The problem I am having is that MysqlWorkbench gives me the following error.
Error Code: 2013. Lost connection to MySQL server during query
Is the syntax I provided correct? Would it update the orders how I expect it to? I can run this from the command line, but it runs for a long time and I'm not sure it's really doing what it's supposed to be doing.
I'm fairly new to mysql, so some clarification on how I could accomplish this would be wonderful.
SHOW CREATE TABLE orders - note there are some garbage columns on this. Needs a lot of cleaning up. The city / state column I refer to in posted query is
CREATE TABLE `orders` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned DEFAULT NULL, `company_id` int(10) unsigned DEFAULT NULL, `action_menu` text COLLATE utf8_unicode_ci, `oCity` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `oState` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, -- many other columns `latitude` double(8,2) DEFAULT NULL, `longitude` double(8,2) DEFAULT NULL, -- more columns PRIMARY KEY (`id`), KEY `orders_company_id_foreign` (`company_id`), CONSTRAINT `orders_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=70120 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `cities_extended` ( `city` varchar(50) NOT NULL, `state_code` char(2) NOT NULL, `zip` int(5) unsigned zerofill NOT NULL, `latitude` double NOT NULL, `longitude` double NOT NULL, `county` varchar(50) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1
Running the query in question from the command line runs for a while, then returns the following error:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
^ this might be due to the process still running. I killed the process and it's actually going through now. Just waiting to see if I get the same 2013 error.