MySQL Error Resolved – Subquery Returns More Than 1 Row

July 26th, 2012 - Posted by Steve Marks to MySQL, Web Development.

If you’ve found this post you’re probably trying to find out why your MySQL query is returning the following error:

Subquery returns more than 1 row

To allow me to explain the problem let me show you an example of a query that might throw this kind of error:

SELECT `col1` FROM `table1`
WHERE `col1` = (SELECT `col2` FROM `table2`);

In the query above you’ll get the error if the secondary query after the WHERE clause return two or more rows.

The Solution

To solve this, depending on your requirement, you have one of two options:

Limit the query

SELECT `col1` FROM `table1`
WHERE `col1` = (SELECT `col2` FROM `table2` LIMIT 1);

Match against the entire resultset

SELECT `col1` FROM `table1`
WHERE `col1` IN (SELECT `col2` FROM `table2`);

Or:

SELECT `col1` FROM `table1`
WHERE `col1` = ANY (SELECT `col2` FROM `table2`);
This entry was posted on Thursday, July 26th, 2012 at 7:14 pm by +Steve Marks and is filed under MySQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed.
Comments...

Fear not, we won't publish this

Comments (4)

No comments have been left yet. Be the first