// MySQL error reference

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
Error message
Subquery returns more than 1 row

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

  1. Use IN (or EXISTS) when several rows are valid.

    SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'NL');
  2. Or reduce the subquery to one row with an aggregate or LIMIT 1 and 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

More syntax and statements errors

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.

// fewer errors, faster fixes

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.