MySQL: ERROR 1242 (21000): Subquery returns more than 1 row

What it means

A subquery in brackets can stand for a single value: after =, < or >, in the select list, or in UPDATE … SET col = (…). That’s a scalar subquery, and it must return at most one row with one column. Error 1242 means it returned two or more, so the server can’t pick one and stops the whole statement.

Two things make this error look random:

It depends on the data. The statement runs fine while every subquery happens to find one row, and fails the first time one finds two: a customer’s second order, a duplicate setting.
No row is fine. A scalar subquery that finds nothing gives NULL, without an error.

Common causes

= where IN was meant: WHERE id = (SELECT user_id FROM orders WHERE …).
A per-row lookup that isn’t unique: (SELECT total FROM orders WHERE user_id = u.id) in the select list, when users can have several orders.
A correlated subquery that isn’t correlated: it forgot to refer to the outer row, so it returns the same set of rows every time.
Duplicates in a table meant to hold one row per key, such as a settings table without a unique index.

How to fix it

Accept every match with IN

If any of the values should match, IN (or = ANY) takes a list:

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 5);

EXISTS does the same and often reads more clearly:

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 5);

Pick one with an aggregate

When you want the largest, smallest, first or a total, say so:

SELECT u.name, (SELECT MAX(o.total) FROM orders o WHERE o.user_id = u.id) AS max_total
FROM users u;

Pick one with ORDER BY … LIMIT 1

For “the latest” or “the first”, order the rows and keep one:

SELECT u.name,
       (SELECT o.total FROM orders o WHERE o.user_id = u.id
        ORDER BY o.created_at DESC LIMIT 1) AS last_total
FROM users u;

Without ORDER BY, LIMIT 1 returns whichever row the server finds first, which can change.

Use a join for more than one column

If you need several columns from the matching row, join instead of writing several subqueries. To keep one row per user, join to the row you want, for example with a window function (MySQL 8.0 and MariaDB 10.2 and later):

SELECT u.name, o.total, o.created_at
FROM users u
JOIN (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
      FROM orders o) o ON o.user_id = u.id AND o.rn = 1;

Find the duplicates

If there should only be one row, find the extra ones, then add a unique index so it can’t happen again:

SELECT user_id, COUNT(*) FROM settings GROUP BY user_id HAVING COUNT(*) > 1;

Reproduce it

On MySQL 8.4.11, with users Ann (two orders) and Bob (one):

SELECT u.name, (SELECT o.total FROM orders o WHERE o.user_id = u.id) AS last_total FROM users u;
SELECT * FROM users WHERE id = (SELECT user_id FROM orders WHERE total > 5);
UPDATE users SET name = (SELECT CONCAT('x', total) FROM orders WHERE user_id = 1) WHERE id = 2;

ERROR 1242 (21000): Subquery returns more than 1 row
ERROR 1242 (21000): Subquery returns more than 1 row
ERROR 1242 (21000): Subquery returns more than 1 row

The ORDER BY created_at DESC LIMIT 1 version returned Ann 25.00 and Bob 7.00, as did MAX(); IN returned both users. A subquery that matched no orders returned no rows rather than an error.

MariaDB 11.4.13 gave the same results and the same error.

In Inlet

When a query fails, Inlet shows the error and links to this page. The query editor runs the statement under the cursor (⌘↩), which makes it quick to run the subquery on its own and see how many rows it returns. With your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the statement.

ERROR 1242 (21000): Subquery returns more than 1 row