Oracle errors

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

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 ORA-01427 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 of ORA-01427

How to fix ORA-01427

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

ORA-01427 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.

Related errors

ORA-01422ORA-01403ORA-00936 All Oracle errors →

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