如何使用內部聯接的第一條記錄重寫更新查詢以更新？

我寫了一個關於inner join上週嘗試進行更新的問題，得到了很多我目前正在嘗試的答案。 這是我曾經得到的答案的連結，這似乎只是問題的一半。

我相信我找到了問題的另一半，即我的cities_extended表格中每個城市/州都有多個條目，因為有些城市有多個郵政編碼，因此我的cities_extended數據庫中有多個條目。當我執行以下查詢時，由於我的表的和列update上的匹配過多，它掛起。city``state_code``cities_extended

update ProcurementPortal.orders as orders
inner join 
ProcurementPortal.cities_extended as geo 
on trim(orders.oCity) = trim(geo.city)
and trim(orders.oState) = trim(geo.state_code)
set
orders.oLat = geo.latitude,
orders.oLon = geo.longitude
where orders.id < 1001
and orders.id > 0;

執行以下select查詢會很好地返回行，但是它會為orders表中匹配的每個條目返回表中每一行的副本。 cities_extended：

SELECT * FROM ProcurementPortal.orders
inner join 
ProcurementPortal.cities_extended as geo 
on trim(orders.oCity) = trim(geo.city)
and trim(orders.oState) = trim(geo.state_code)
where orders.id < 1001
and orders.id > 0
limit 1000;

將其鏡像到 時update，會導致update“掛起”，或者執行時間過長。嘗試執行它時出現超時。

我怎樣才能重寫它，使它只使用cities_extended表中的第一個匹配項？我的orders桌子上沒有郵政編碼，因此不能將其包含在我的標準中。同時，我需要orders更新這張表。

編輯：

輸出show create table orders：

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(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `oState` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
 `oAddress` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `oZone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `oLat` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `oLon` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `oAvailableTime` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `dCity` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `dState` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `dAddress` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `dZone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `dLat` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `dLon` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `dAvailableTime` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `mcleodEquipmentCode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `dropTrailer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `volume` int(11) DEFAULT NULL,
 `volumeType` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `oStateZone` int(11) DEFAULT NULL,
 `dStateZone` int(11) DEFAULT NULL,
 `customerId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `filepath` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `orderType` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `rate` double(8,2) NOT NULL DEFAULT ''0.00'',
 `rateType` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `fsc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `originalFile` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `owner_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `latitude` double(8,2) DEFAULT NULL,
 `longitude` double(8,2) DEFAULT NULL,
 `destLatitude` double(8,2) DEFAULT NULL,
 `destLongitude` double(8,2) DEFAULT NULL,
 `max_dead_head` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `created_at` timestamp NOT NULL DEFAULT ''0000-00-00 00:00:00'',
 `updated_at` timestamp NOT NULL DEFAULT ''0000-00-00 00:00:00'',
 `d_max_dead_head` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `commodity` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
 `hasMatches` varchar(5) COLLATE utf8_unicode_ci DEFAULT ''false'',
 PRIMARY KEY (`id`),
 KEY `orders_company_id_foreign` (`company_id`),
 KEY `state_city_ix` (`oState`,`oCity`),
 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

輸出show create table cities_extended：

CREATE TABLE `cities_extended` (
 `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `state_code` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `zip` int(5) unsigned zerofill NOT NULL,
 `latitude` double NOT NULL,
 `longitude` double NOT NULL,
 `county` varchar(50) NOT NULL,
 KEY `state_city_ix` (`state_code`,`city`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

trim如果您在查詢中使用該函式，則不會使用 (state, city) 上的複合索引。您可能需要先更新兩個表中的兩個欄位：

UPDATE orders SET oState=TRIM(oState), oCity=TRIM(oCity);
UPDATE cities_extended SET state_code=TRIM(state_code), city=TRIM(city);

然後在沒有的情況下執行查詢trim

附帶說明一下，您的索引最好在州之前切換為城市，因為城市具有更高的基數

$$ more different values $$.

引用自：https://dba.stackexchange.com/questions/111286