How to resolve ORA-01427 Error Message in Oracle database

Learn the purpose and how to get to the bottom of the ORA-01427 error message in Oracle.

Description

When you stumble upon an ORA-01427 error, the following error message will appear:

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

Cause

You tried to execute a SQL announcement that contained a SQL subquery that returns more than one row.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Rewrite your question so that the subquery solely returns one row.

Option #2

Change your question to use one of the following with your subquery results:

ANY

ALL

IN

NOT IN

For example, if you tried to execute the following SQL statement:

SELECT *
FROM orders
WHERE supplier_id = (SELECT supplier_id
                     FROM suppliers
                     WHERE supplier_name = 'IBM');

And there used to be more than one document in the suppliers table with the supplier_name of IBM, you would acquire the following message:

The most frequent way to correct this SQL assertion is to use the IN situation as follows:

SELECT *
FROM orders
WHERE supplier_id IN (SELECT supplier_id
                      FROM suppliers
                      WHERE supplier_name = 'IBM');