EC
1988
MariaDBERRORNotableQueryHIGH confidence

Subquery returns more than one row

Production Risk

Low — query is rejected; no data is modified.

What this means

A scalar subquery (used where a single value is expected, e.g. in a comparison with =) returned more than one row. Scalar subqueries must return at most one row.

Why it happens
  1. 1Scalar subquery in WHERE col = (SELECT ...) returns multiple rows.
  2. 2Missing LIMIT 1 on a subquery that could return multiple rows.
  3. 3Subquery used in SET clause of UPDATE returns multiple rows.
How to reproduce
trigger — this will error
trigger — this will error
SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE status = 'active');

expected output

ERROR 1988 (21000): Subquery returns more than 1 row.

Fix 1

Use IN instead of = for multi-row subqueries

Use IN instead of = for multi-row subqueries
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');

Why this works

IN handles multiple rows correctly; = requires exactly one row.

Fix 2

Add LIMIT 1 if only one row is needed

Add LIMIT 1 if only one row is needed
SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE status = 'active' LIMIT 1);

Why this works

LIMIT 1 ensures the subquery returns at most one row.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1988 ER_SUBQUERY_NO_1_ROW

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MariaDB errors