子查询在update语句中使用外键返回多个行

Question

我试图通过指定表中的外键值来更新MYSQL中的表。例如，我有表Person, Location, Job。我已经在Job表中添加了一个position列，并基于PersonId, LocationId，这是作业表中的外键和作业的pay，我想更新position。我已经尝试了以下SQL查询来实现这一点:

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

我得到错误"子查询返回多于一行"。有办法解决这个问题吗?任何帮助都会很感激。谢谢你。

Answer 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);

Answer 2

这是因为你可能有两个叫john的人。在这种情况下，你可以使用where in

象下面这样

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

否则尝试将子查询响应限制为一个。如下

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