// Oracle error reference

ORA-01427: single-row subquery returns more than one row

A subquery used where one value is expected, after = or in the select list, returned several rows.

Error code
ORA-01427
Category
Syntax and SQL statements
Error message
single-row subquery returns more than one row

What it means

A subquery after =, <, > or in a SET or select list must return at most one row. ORA-01427 means it returned more, so Oracle can't pick a value.

Common causes

  • A lookup that matches several rows, for example by a non-unique name.
  • Missing correlation to the outer row, so the subquery returns every row.
  • Duplicate rows in a table that was assumed to have one row per key.

How to fix it

  1. Use IN if any of the values may match.

    SELECT * FROM hr.employees
    WHERE department_id IN (SELECT department_id FROM hr.departments WHERE location_id = 1700);
  2. Make the subquery return one row: correlate it, filter on a unique key, or aggregate.

    UPDATE orders o
    SET region = (SELECT c.region FROM customers c WHERE c.id = o.customer_id);
  3. Find the keys that have duplicates.

    SELECT name, COUNT(*) FROM customers GROUP BY name HAVING COUNT(*) > 1;

Related errors

More syntax and sql statements errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.