子查询返回超过 1 行 - MySQL [英] Subquery returns more than 1 row - MySQL

问题描述

UPDATE `pams_faker_lead_location` 
SET `location` = ( SELECT location FROM pams_leads WHERE pams_leads.location_id = pams_faker_lead_location.id   )

我不知道当我在 Mysql 中运行查询时，出现此错误.

I dont know when i run the query in Mysql , this error occur.

#1242 - 子查询返回超过 1 行

谁能帮我解决问题?

推荐答案

您没有收到错误消息的哪一部分?子查询返回不止一行，因此您需要决定哪个您想要的值.以下是获取一行的两种常用方法:

What part of the error message do you not get? The subquery is returning more than one row, so you need to decide which value you want. Here are two common methods for getting one row:

使用聚合函数，例如 MAX() 或 MIN():

One uses an aggregation function, such as MAX() or MIN():

UPDATE pams_faker_lead_location fll 
    SET location = (SELECT MAX(l.location)
                    FROM pams_leads l
                    WHERE l.location_id = fll.id
                    LIMIT 1
                   );

另一个使用LIMIT.这通常也有一个 ORDER BY，所以你可以选择你想要的值:

The other uses LIMIT. This would normally have an ORDER BY as well, so you can choose the value you want:

UPDATE pams_faker_lead_location fll 
    SET location = (SELECT l.location
                    FROM pams_leads l
                    WHERE l.location_id = fll.id
                    LIMIT 1
                   );

发生错误是因为数据不是您期望的那样.使用诸如 JOIN 之类的方法只会隐藏问题 -- 如果存在大量重复项，可能会导致严重的性能损失.

The error is occurring because the data is not what you expect it to be. Using a method such as JOIN merely hides the problem -- and might incur significant performance penalties if there are lots and lots of duplicates.

这篇关于子查询返回超过 1 行 - MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！