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
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
-
Use
INif any of the values may match.SELECT * FROM hr.employees WHERE department_id IN (SELECT department_id FROM hr.departments WHERE location_id = 1700);
-
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);
-
Find the keys that have duplicates.
SELECT name, COUNT(*) FROM customers GROUP BY name HAVING COUNT(*) > 1;
Related errors
ORA-01422exact fetch returns more than requested rows512SQL Server: Subquery returned more than 1 value1242MySQL: Subquery returns more than 1 row21000PostgreSQL: more than one row returned by a subquery
More syntax and sql statements errors
ORA-00933SQL command not properly endedORA-00936missing expressionORA-00907missing right parenthesisORA-00911invalid characterORA-00917missing commaORA-00918column ambiguously definedORA-00923FROM keyword not found where expectedORA-00937not a single-group group function
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.