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 returned two or more rows.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-13

ORA-01427 happens when a subquery sits in a place that takes exactly one value — after =, inside the SELECT list, or in UPDATE ... SET col = (...) — and returns more than one row at run time.

The SQL itself is valid, so the same query can work for months and then fail when the data changes. Run the subquery on its own first and see how many rows it returns. The fix depends on whether you really want several rows (IN) or only one of them (add a condition, or pick one row).

Causes and fixes

  1. Comparing with = to a subquery that has GROUP BY
  2. A scalar subquery in the SELECT list reads a history table
  3. UPDATE SET subquery is missing the join to the row being updated

1Comparing with = to a subquery that has GROUP BY

WHERE salary = (SELECT MAX(salary) FROM emp GROUP BY dept_id) was meant to find the top earner of each department. But GROUP BY dept_id returns one row per department. Running the subquery alone gave 2 rows (5000 for dept 10, 6100 for dept 20), and = cannot compare with two values.

If you want all of those values, use IN instead of =. With IN the query returned Kim and Park, the top earner in each department.

Setup
Setuprun as lab_a
CREATE TABLE dept (dept_id NUMBER PRIMARY KEY, dept_name VARCHAR2(20));
INSERT INTO dept VALUES (10, 'Sales');
INSERT INTO dept VALUES (20, 'IT');
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER, salary NUMBER);
INSERT INTO emp VALUES (1, 'Kim', 10, 5000);
INSERT INTO emp VALUES (2, 'Lee', 10, 4200);
INSERT INTO emp VALUES (3, 'Park', 20, 6100);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT name
  FROM emp
 WHERE salary = (SELECT MAX(salary) FROM emp GROUP BY dept_id);
OUTPUT
 WHERE salary = (SELECT MAX(salary) FROM emp GROUP BY dept_id)
                 *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
Help: https://docs.oracle.com/error-help/db/ora-01427/
Checkrun as lab_a
SELECT dept_id, MAX(salary) AS max_sal FROM emp GROUP BY dept_id ORDER BY dept_id;
OUTPUT
   DEPT_ID    MAX_SAL
---------- ----------
        10       5000
        20       6100

2 rows selected.

Before choosing between = and IN, run the subquery alone. If it returns several rows and you need all of them, IN is the right operator.

After the fixrun as lab_a
SELECT name
  FROM emp
 WHERE salary IN (SELECT MAX(salary) FROM emp GROUP BY dept_id)
 ORDER BY name;
OUTPUT
NAME
------------------------------
Kim
Park

2 rows selected.

2A scalar subquery in the SELECT list reads a history table

(SELECT a.addr FROM emp_addr a WHERE a.emp_id = e.emp_id) assumes one address per employee. emp_addr keeps every change, and employee 1 had 2 rows (HAVING COUNT(*) > 1 found it). The error points at the subquery on line 2.

Decide which row you want and say so in the subquery. Here the latest one: ORDER BY a.updated DESC FETCH FIRST 1 ROW ONLY. All 3 employees came back, with Kim showing the newer address, Seoul Mapo.

Setup
Setuprun as lab_a
CREATE TABLE dept (dept_id NUMBER PRIMARY KEY, dept_name VARCHAR2(20));
INSERT INTO dept VALUES (10, 'Sales');
INSERT INTO dept VALUES (20, 'IT');
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER, salary NUMBER);
INSERT INTO emp VALUES (1, 'Kim', 10, 5000);
INSERT INTO emp VALUES (2, 'Lee', 10, 4200);
INSERT INTO emp VALUES (3, 'Park', 20, 6100);
CREATE TABLE emp_addr (emp_id NUMBER, addr VARCHAR2(30), updated DATE);
INSERT INTO emp_addr VALUES (1, 'Seoul Gangnam', DATE '2025-01-10');
INSERT INTO emp_addr VALUES (1, 'Seoul Mapo', DATE '2026-03-02');
INSERT INTO emp_addr VALUES (2, 'Busan', DATE '2026-05-20');
INSERT INTO emp_addr VALUES (3, 'Incheon', DATE '2026-02-11');
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


Table created.


1 row created.


1 row created.


1 row created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT e.name,
       (SELECT a.addr FROM emp_addr a WHERE a.emp_id = e.emp_id) AS addr
  FROM emp e
 ORDER BY e.emp_id;
OUTPUT
       (SELECT a.addr FROM emp_addr a WHERE a.emp_id = e.emp_id) AS addr
        *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
Help: https://docs.oracle.com/error-help/db/ora-01427/
Checkrun as lab_a
SELECT emp_id, COUNT(*) AS cnt FROM emp_addr GROUP BY emp_id HAVING COUNT(*) > 1;
OUTPUT
    EMP_ID        CNT
---------- ----------
         1          2

1 row selected.

Check for duplicate keys with GROUP BY ... HAVING COUNT(*) > 1 before choosing a fix. It shows which rows break the one-row assumption.

After the fixrun as lab_a
SELECT e.name,
       (SELECT a.addr FROM emp_addr a WHERE a.emp_id = e.emp_id
         ORDER BY a.updated DESC FETCH FIRST 1 ROW ONLY) AS addr
  FROM emp e
 ORDER BY e.emp_id;
OUTPUT
NAME                           ADDR
------------------------------ ------------------------------
Kim                            Seoul Mapo
Lee                            Busan
Park                           Incheon

3 rows selected.

3UPDATE SET subquery is missing the join to the row being updated

The subquery in SET salary = (SELECT n.salary FROM emp_new_sal n WHERE n.apply_dt = DATE '2026-11-01') filters only by date. It has nothing that ties it to the employee being updated, so it returns every row for that date — 2 rows here (employees 1 and 2).

Add AND n.emp_id = e.emp_id so each employee gets their own row. Also limit the UPDATE with WHERE e.emp_id IN (...); otherwise employees with no new salary would be set to NULL. After the change, 2 rows were updated and employee 3 kept 6100.

Setup
Setuprun as lab_a
CREATE TABLE dept (dept_id NUMBER PRIMARY KEY, dept_name VARCHAR2(20));
INSERT INTO dept VALUES (10, 'Sales');
INSERT INTO dept VALUES (20, 'IT');
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER, salary NUMBER);
INSERT INTO emp VALUES (1, 'Kim', 10, 5000);
INSERT INTO emp VALUES (2, 'Lee', 10, 4200);
INSERT INTO emp VALUES (3, 'Park', 20, 6100);
CREATE TABLE emp_new_sal (emp_id NUMBER, salary NUMBER, apply_dt DATE);
INSERT INTO emp_new_sal VALUES (1, 5200, DATE '2026-10-01');
INSERT INTO emp_new_sal VALUES (1, 5500, DATE '2026-11-01');
INSERT INTO emp_new_sal VALUES (2, 4400, DATE '2026-11-01');
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


Table created.


1 row created.


1 row created.


1 row created.


Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
UPDATE emp e
   SET salary = (SELECT n.salary FROM emp_new_sal n
                  WHERE n.apply_dt = DATE '2026-11-01')
 WHERE e.emp_id = 1;
OUTPUT
   SET salary = (SELECT n.salary FROM emp_new_sal n
                 *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
Help: https://docs.oracle.com/error-help/db/ora-01427/
Checkrun as lab_a
SELECT emp_id, salary FROM emp_new_sal WHERE apply_dt = DATE '2026-11-01' ORDER BY emp_id;
OUTPUT
    EMP_ID     SALARY
---------- ----------
         1       5500
         2       4400

2 rows selected.

Run the subquery as a plain SELECT for one employee before running the UPDATE. If it returns more than one row there, the UPDATE will fail too.

After the fixrun as lab_a
UPDATE emp e
   SET salary = (SELECT n.salary FROM emp_new_sal n
                  WHERE n.apply_dt = DATE '2026-11-01'
                    AND n.emp_id = e.emp_id)
 WHERE e.emp_id IN (SELECT emp_id FROM emp_new_sal WHERE apply_dt = DATE '2026-11-01');
SELECT emp_id, salary FROM emp ORDER BY emp_id;
ROLLBACK;
OUTPUT
2 rows updated.


    EMP_ID     SALARY
---------- ----------
         1       5500
         2       4400
         3       6100

3 rows selected.


Rollback complete.

FAQ

How do I find which subquery causes ORA-01427?

The * under the error points at the start of the failing subquery. Copy that subquery, run it alone (with a sample value for any outer column), and count the rows.

Should I just add ROWNUM = 1 to fix ORA-01427?

Only if any one of the rows is acceptable. If a specific row is correct (for example the latest), say so with ORDER BY ... FETCH FIRST 1 ROW ONLY, as in the history-table example.

Why did the query work yesterday and fail today?

ORA-01427 is a run-time error. The SQL is valid; the data changed so the subquery now returns two rows, such as a second address row for the same employee.

What is the difference between = and IN with a subquery?

= accepts exactly one value from the subquery. IN accepts any number of rows. In this lab, changing = to IN returned the 2 expected rows.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors