MySQL error 1242: Subquery returns more than 1 row
A subquery used as a single value, for example after = or in the select list, returned several rows.
- Error number
1242- Name
ER_SUBQUERY_NO_1_ROW- SQLSTATE
21000- Category
- Syntax and statements
What it means
A scalar subquery must return at most one row. If it returns more, MySQL can't turn it into one value and raises 1242. The query may have worked for months and fail now because the data changed.
Common causes
- Comparing with
=to a subquery that can match several rows. - A lookup subquery in the select list on a column that isn't unique.
- Duplicate rows that weren't there when the query was written.
How to fix it
-
Use
IN(orEXISTS) when several rows are valid.SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'NL');
-
Or reduce the subquery to one row with an aggregate or
LIMIT 1and a defined order.SELECT o.id, (SELECT p.amount FROM payments p WHERE p.order_id = o.id ORDER BY p.paid_at DESC LIMIT 1) AS last_payment FROM orders o;
Related errors
512SQL Server: Subquery returned more than 1 valueORA-01427Oracle: single-row subquery returns more than one row21000PostgreSQL: more than one row returned by a subquery
More syntax and statements errors
1064You have an error in your SQL syntax1149SQL syntax error1052Column is ambiguous1055Expression isn't in GROUP BY1093Can't specify target table for update1136Column count doesn't match value count1248Every derived table must have its own alias1318Incorrect number of arguments
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.