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. Use IN or EXISTS, aggregate the subquery, or make it return a single row.

What it means

A scalar subquery stands in for one value. If, for any row of the outer query, it returns two or more rows, Oracle cannot pick one and stops with ORA-01427. The statement may work for some data and fail for other data, which makes the error look random.

Common causes

Comparing with = to a subquery that is not on a unique key.
A correlated subquery in the SELECT list missing part of its correlation.
Duplicate rows in a lookup table.
An UPDATE … SET col = (subquery) where the subquery matches several rows.

How to fix it

If any of several values is fine, use IN or EXISTS instead of =.
If you need one value, aggregate (MAX, MIN) or use FETCH FIRST 1 ROW ONLY with an ORDER BY.
Complete the correlation so the subquery is unique per outer row.
Find the duplicates: GROUP BY key HAVING COUNT(*) > 1.

Example

Use IN instead of =:

-- ORA-01427 when a department has several managers
SELECT * FROM employees
 WHERE manager_id = (SELECT id FROM managers WHERE dept_id = 10);

SELECT * FROM employees
 WHERE manager_id IN (SELECT id FROM managers WHERE dept_id = 10);

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-01427. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.