EC
1400
MySQLerrorqueryhigh confidence

Subquery returns more than 1 row

Production Risk

Low — query fails; no data changed.

What this means

A scalar subquery used in a SELECT list, WHERE clause, or SET returned more than one row.

Why it happens
  1. 1SELECT (SELECT col FROM t) — table has multiple rows
  2. 2Scalar subquery in SET clause matches multiple rows
How to reproduce
trigger — this will error
trigger — this will error
SELECT (SELECT name FROM users WHERE active = 1);

expected output

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

Fix 1

Add LIMIT 1

Add LIMIT 1
SELECT (SELECT name FROM users WHERE active = 1 LIMIT 1);

Why this works

Forces at most one row from the subquery.

Fix 2

Use an aggregate

Use an aggregate
SELECT (SELECT MAX(name) FROM users WHERE active = 1);

Why this works

Returns a single scalar value.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1242 / 1400 ER_SUBQUERY_NO_1_ROW

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

← All MySQL errors