Subquery returns more than 1 row 🗃️ SQL

Subquery returns more than 1 row (MySQL 1242) / more than one row returned by a subquery used as an expression

A subquery used where a single value is expected (= (SELECT …)) returned several rows.

Seen on: MySQL PostgreSQL SQL

Meaning

Scalar subqueries must return at most one row. Data that “should” be unique (one address per user, one active price) often isn’t, and the query breaks the day a duplicate appears.

Common causes

  • Duplicate data where you assumed uniqueness
  • Missing condition in the subquery
  • Using = where IN was intended

⚡ Quick fix

  1. Use IN (…) / EXISTS if multiple rows are valid
  2. Add ORDER BY … LIMIT 1 when you want one specific row
  3. Add a UNIQUE constraint if duplicates are a data bug

Detailed fix by platform

SQL

  1. Pick one row deliberately, or use IN:
    sql
    SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

SELECT u.*, (SELECT a.city FROM addresses a WHERE a.user_id = u.id ORDER BY a.is_primary DESC, a.id LIMIT 1) AS city
FROM users u;

How to diagnose

  1. Subquery — Run it alone — how many rows?
  2. Intent — One value or a set?
  3. Data — Should a constraint prevent duplicates?

🧠 Still stuck? Analyze your error

Paste the full message, response headers or stack trace — we'll detect the platform and point to the most likely cause.