How to Fix MySQL Error 1242: Subquery Returns More Than 1 Row

ERROR 1242 (21000): Subquery returns more than 1 row is raised when a subquery used in a scalar context — such as the right-hand side of a =, <, >, or != comparison — returns more than one row. The error symbol is ER_SUBQUERY_NO_1_ROW.

Impact

MySQL requires that any subquery appearing where a single value is expected returns exactly one row. When a subquery in that position returns two or more rows, the engine cannot reduce it to a scalar value, so the entire statement is aborted and this error is returned. No rows are read, written, or modified.

This error appears most often in WHERE clauses that use = with a subquery, in SET clauses of UPDATE statements, and in SELECT column expressions. ORMs such as Django ORM, Hibernate, and ActiveRecord can surface it as a generic database exception with the underlying message in the cause chain; the original ERROR 1242 text is always present in the driver-level error.

Common Causes

Using = with a subquery that can match multiple rows. The most frequent trigger. The subquery has no LIMIT 1 and the table contains multiple matching rows.
A subquery that was previously unique is no longer unique. The schema or data has changed — for example, a UNIQUE constraint was dropped, or duplicate rows were inserted — so a query that worked before now returns multiple rows.
Correlated subquery that fans out. A correlated subquery in a SELECT list or SET clause that joins back to a parent table without a sufficiently selective WHERE condition, returning multiple rows per outer row.
IN rewritten by hand as =. A developer intended IN (subquery) but accidentally wrote = (subquery), which is only valid when the subquery returns a single row.
Scalar subquery inside a CASE expression or function argument. Subqueries used as arguments to functions (e.g., COALESCE, IF) or inside CASE WHEN branches are also subject to the scalar constraint.

Troubleshooting and Resolution Steps

Identify which subquery is at fault. Run the inner subquery in isolation to see how many rows it returns:

-- Original failing query
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'US');

-- Run the subquery alone to inspect results
SELECT id FROM customers WHERE country = 'US';

Use IN instead of = when multiple matches are valid. This is the most common fix when you want to match any of the returned values:

-- Before (fails if more than one customer exists in 'US')
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'US');

-- After
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US');

Use ANY or ALL for inequality comparisons.

-- Match rows where amount is greater than any order from customer 42
SELECT * FROM orders
WHERE amount > ANY (SELECT amount FROM orders WHERE customer_id = 42);

Add LIMIT 1 when you genuinely only want one row. Use this only when any single matching row is acceptable and the choice of which one is arbitrary, or when you add an ORDER BY to make the choice deterministic:

-- Assign a product to the most recently created category that matches
UPDATE products
SET category_id = (
    SELECT id FROM categories
    WHERE name = 'Electronics'
    ORDER BY created_at DESC
    LIMIT 1
)
WHERE product_id = 99;

Rewrite as a JOIN for correlated subqueries. If you are assigning a column in a SELECT or UPDATE from a related table, a JOIN is often cleaner and avoids the scalar constraint entirely:

-- Before (fails when a customer has multiple active contracts)
SELECT o.id,
       (SELECT c.plan FROM contracts c WHERE c.customer_id = o.customer_id AND c.active = 1) AS plan
FROM orders o;

-- After (returns multiple rows per order if needed, or add LIMIT in the subquery)
SELECT o.id, c.plan
FROM orders o
JOIN contracts c ON c.customer_id = o.customer_id AND c.active = 1;

Use aggregate functions to collapse multiple rows into one. When the subquery must return a derived scalar, use MAX, MIN, COUNT, or similar:
```
SELECT * FROM orders
WHERE amount > (SELECT MAX(amount) FROM orders WHERE status = 'refunded');
```

Use EXISTS instead of a value comparison when you only need presence:

SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'pending');

Additional Information

The SQLSTATE code 21000 (CARDINALITY VIOLATION) is the SQL standard category for this class of error; MySQL maps error 1242 to this state.
MySQL is stricter than some other databases on this point. PostgreSQL raises ERROR: more than one row returned by a subquery used as an expression for the same scenario.
When the subquery returns zero rows, MySQL returns NULL rather than an error for = comparisons — only the multiple-row case raises 1242.
In stored procedures and prepared statements, the error is raised at execution time, not at parse or prepare time, so it may only surface under certain data conditions.
Error 1242 is unrelated to sql_mode — it is enforced regardless of strict mode settings.

Frequently Asked Questions

Why does the query work in development but fail in production? Development environments often have minimal seed data, so the subquery happens to return only one row. Production data grows, eventually producing duplicates that trigger the error. Add a UNIQUE constraint if exactly one match is guaranteed by business logic, or rewrite using IN/JOIN to handle the general case.

Can I just add LIMIT 1 to silence the error? You can, but it may hide a data quality problem or return the wrong row. Only use LIMIT 1 when any single matching row is semantically correct, or when combined with ORDER BY to make the selection deterministic. For most cases, rewriting with IN or a JOIN is the safer fix.

Does MySQL ever automatically pick one row from a multi-row subquery? No. Unlike some permissive databases, MySQL strictly rejects the statement. There is no @@sql_mode flag that suppresses error 1242.

How do I find all places in my codebase that might be affected? Search for patterns like = (SELECT, != (SELECT, < (SELECT, and > (SELECT in your SQL files and ORM query builders. Any scalar comparison against a subquery is a candidate — verify that the subquery is guaranteed to return at most one row, either through a UNIQUE constraint, an aggregate function, or explicit LIMIT 1.