Result consisted of more than one row (subquery)
Production Risk
Medium — query fails; no data is affected.
ER_MULTI_ROWS_FROM_SELECT_CONDITION (1172, SQLSTATE 42000) is raised when a scalar subquery (used where a single value is expected) returns more than one row.
- 1Scalar subquery in SELECT, WHERE, or SET returns multiple rows
- 2Subquery in an assignment (SET @var = (SELECT ...)) matches more than one row
- 3Correlated subquery returns multiple rows for a given outer row
SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE status = 'active'); -- multiple active customers exist
expected output
ERROR 1172 (42000): Result consisted of more than one row
Fix 1
Use IN instead of = for multi-row subqueries
WHEN The subquery may legitimately return multiple rows.
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');
Why this works
IN handles a set of values; = requires exactly one value. Use IN when multiple matches are expected.
Fix 2
Add LIMIT 1 to force a single row
WHEN Only the first matching row is needed.
SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE status = 'active' LIMIT 1);
Why this works
LIMIT 1 guarantees at most one row is returned, but be aware this picks an arbitrary row if multiple exist.
✕ Add LIMIT 1 to all scalar subqueries without understanding which row is picked
LIMIT 1 without ORDER BY returns an arbitrary row; if the correct single row matters, add an appropriate ORDER BY or fix the WHERE clause.
MySQL 8.0 — 1172 ER_MULTI_ROWS_FROM_SELECT_CONDITION
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev