mysql - 更新基于存在多个匹配项的公共(public)值

Question

我有两个表(MySQL 数据库)，一个称为城市(有列“state_id”和“stateAB”——state_id 是我要填充的行，stateAB 是州的 2 个字母代码) --我希望将其作为关键值)。

我有另一个名为 states 的表(其中包含“id”列[这是我想要进入“cities”的“state_id”字段的值]，以及一个“title”字段[2 个字母的州代码] 为公共(public)键值)。

我想使用一个简单的:

UPDATE cities SET state_id=(SELECT id FROM states WHERE states.title=cities.stateAB)

我们的想法是，state_id 将被设置为 2 字母代码匹配时返回的 id。

问题是返回以下内容:

#1242 - Subquery returns more than 1 row

我认为这是因为每个州的代码匹配不止一次，原因很简单，每个州有多个城市(并且它们都具有相同的州/代码)。

我不确定如何更改它以使其正常工作 - 我确信这是我所缺少的明显内容，但我不知道如何处理这个问题。

Answer 1

这是您的查询:

UPDATE cities
    SET state_id = (SELECT id FROM states WHERE states.title = cities.stateAB);

您收到错误是因为 states 在 title 列中有重复项。您可以通过运行找到这些:

select title, count(*) as numdups
from states
group by title
having count(*) > 1;

您可能不关心重复项，很乐意在匹配时仅选择一个 ID(一致)。如果是这样，您可以这样做:

UPDATE cities
    SET state_id = (SELECT MIN(id) FROM states WHERE states.title = cities.stateAB);