Oracle Error ORA-01427

Error code

ORA-01427

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (same wording in every release)

Category

SQL Syntax & Query Structure

Last reviewed

24 October 2026

What it means

ORA-01427 means a subquery used where only one value is allowed, such as after = or < or as a column in the SELECT list or an UPDATE ... SET, returned two or more rows when the statement ran.

A comparison such as WHERE customer_id = (SELECT ...), a scalar subquery in the SELECT list, and SET price = (SELECT ...) in an UPDATE all expect exactly one value. Oracle's reference says that when the subquery returns more than one row, the outer query must use ANY, ALL, IN or NOT IN instead, or the query must be reworded so only one row is retrieved.

The error is raised at run time and depends on the data: the same statement can work for months and then fail the day a second matching row is inserted. A subquery that returns no rows does not raise ORA-01427; in a comparison or as a scalar value it gives NULL. The message never says which subquery or which key returned several rows, so the diagnostics below find them.

Common causes

• WHERE col = (SELECT ...) where the subquery can return several rows: a name, email or code that is not unique.
• A scalar subquery in the SELECT list looks up one value per row, but the lookup table has duplicates for some keys.
• UPDATE ... SET col = (SELECT ... WHERE key = outer.key) where the source table has more than one row per key.
• The correlation condition is missing or incomplete, for example a subquery on order_lines correlated only by product and not by order.
• A lookup table that used to be unique gained duplicates because it has no primary key or unique constraint on the lookup column.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT customer_id
FROM shop.customers
WHERE name = 'Sara Ahmed';

SELECT sku, COUNT(*) AS copies
FROM shop.price_list
GROUP BY sku
HAVING COUNT(*) > 1
ORDER BY copies DESC;

SELECT c.constraint_name, c.constraint_type, cc.column_name
FROM all_constraints c
JOIN all_cons_columns cc
  ON cc.owner = c.owner AND cc.constraint_name = c.constraint_name
WHERE c.owner = 'SHOP'
  AND c.table_name = 'PRICE_LIST'
  AND c.constraint_type IN ('P', 'U');

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT order_id, amount
FROM shop.orders
WHERE customer_id IN (SELECT customer_id
                      FROM shop.customers
                      WHERE name = 'Sara Ahmed');

SELECT o.order_id,
       (SELECT p.price
        FROM shop.price_list p
        WHERE p.sku = o.sku
        ORDER BY p.valid_from DESC
        FETCH FIRST 1 ROW ONLY) AS current_price
FROM shop.order_lines o;

SELECT o.order_id, o.sku,
       (SELECT p.price
        FROM shop.price_list p
        WHERE p.sku = o.sku
          AND p.currency = o.currency) AS price
FROM shop.order_lines o;

MERGE INTO shop.products t
USING (SELECT sku, MAX(price) KEEP (DENSE_RANK LAST ORDER BY valid_from) AS price
       FROM shop.price_list
       GROUP BY sku) s
ON (t.sku = s.sku)
WHEN MATCHED THEN UPDATE SET t.price = s.price;

ALTER TABLE shop.price_list
  ADD CONSTRAINT price_list_sku_from_uk UNIQUE (sku, valid_from);

Examples

Comparing with = against a non-unique name

Not reproduced here: no Oracle Database is installed for this site. The result shows the message as documented in Oracle's error reference, which uses the same wording in 26ai, 21c and 19c. Changing = to IN returns the orders of both customers.

SELECT order_id, amount
FROM orders
WHERE customer_id = (SELECT customer_id
                     FROM customers
                     WHERE name = 'Sara Ahmed');
-- two customers are called Sara Ahmed

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

An UPDATE whose source has two rows for one key

Not reproduced here: no Oracle Database is installed for this site. The message format is taken from Oracle's error reference. The whole UPDATE fails and no row is changed, even though most products have only one price.

UPDATE products p
SET price = (SELECT l.price
             FROM price_list l
             WHERE l.sku = p.sku);
-- price_list holds an old and a new price for sku 'A-100'

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

How to prevent it

Before writing = (SELECT ...), check that the inner query is guaranteed to return one row by a primary key or unique constraint on the columns you filter by. If it is not, write IN, an aggregate or an ordered FETCH FIRST from the start.

Put unique constraints on lookup tables such as price lists and code tables, so a duplicate is rejected when it is inserted instead of breaking every query that reads it. See subqueries vs joins for when a join is clearer.

Common questions

Learn more on CodeWithSQL

• SQL subqueries vs joins
• SQL constraints explained

Official documentation

• ORA-01427 (Oracle Database Error Messages)
• Using Subqueries (Oracle SQL Language Reference 19c)
• MERGE (Oracle SQL Language Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.