MySQL UPDATE用外键筛选时报Subquery returns more than one row如何解决

你的SQL中使用=操作符匹配子查询返回结果，当Location表中area = 'NYC'的记录超过1条，或者Person表中name = 'John'的记录超过1条时，子查询返回多个值，=无法匹配多值场景就会触发该报错。另外你原SQL中Person.name = "John漏了右引号，需要先补全避免语法错误。

• 方法1：将=替换为IN操作符，支持匹配子查询返回的多个值，适合需要匹配所有符合子查询条件的记录的场景：

UPDATE Job
SET position = 'Doctor'
WHERE Job.pay = 2000 AND 
    Job.LocationId IN (SELECT Location.LocationId FROM Location WHERE Location.area = 'NYC') AND
    Job.PersonId IN (SELECT Person.PersonId FROM Person WHERE Person.name = 'John');

• 方法2：如果逻辑上确认area = 'NYC'和name = 'John'都应该只对应唯一记录，可以先清理重复数据，或者给子查询增加LIMIT 1强制仅返回1条结果：

UPDATE Job
SET position = 'Doctor'
WHERE Job.pay = 2000 AND 
    Job.LocationId = (SELECT Location.LocationId FROM Location WHERE Location.area = 'NYC' LIMIT 1) AND
    Job.PersonId = (SELECT Person.PersonId FROM Person WHERE Person.name = 'John' LIMIT 1);

*注意：该方法会默认取子查询结果的第一条记录，若存在多条符合条件的记录可能不符合业务预期，建议先校验数据唯一性再使用。

• 方法3（更推荐）：使用多表关联更新的写法，执行效率更高，也能避免子查询多值问题：

UPDATE Job
JOIN Location ON Job.LocationId = Location.LocationId
JOIN Person ON Job.PersonId = Person.PersonId
SET Job.position = 'Doctor'
WHERE Job.pay = 2000 AND Location.area = 'NYC' AND Person.name = 'John';

内容的提问来源于stack exchange，提问作者S23