ORA-01427: Subquery returns more than one row

When you run a SQL Statement with Subquery, you can get ” ORA-01427: Subquery returns more than one row ” error.

 

 

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

Error details are as follows.

 

SELECT *

FROM employee

WHERE emp_id = (SELECT emp_id

FROM person

WHERE person_name = 'Mehmet') ;

 

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

Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.

Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved.

 

 

ORA-01427

 

This error ORA-01427: Subquery returns more than one row is related with the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.

 

 

single-row subquery returns more than one row

If your SQL Statement ( single row subquery ) returns only one row, then you need to use the equal comparison operators (=,<,>,<> ).

But If your SQL Statement ( multiple row subquery ) returns more than one row ( multiple rows ), then you need to use the Multiple-row Sub-query Operators like ( IN, ANY, ALL and etc.. )

 

To solve the above SQL Statement, you can use the IN operator instead of = operator as follows.

SELECT *
FROM employee
WHERE emp_id IN (SELECT emp_id
FROM person
WHERE person_name = 'Mehmet') ;



This solution is valid for both Select and Update, Insert and Delete operations.

If you got this error during the Update, Insert and Delete operations, then Check your Statement if Subquery returns more than one row, then use the Multiple-row Sub-query Operators like ( IN, ANY, ALL and etc.. )

 

Do you want to learn more details about Oracle SQL, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course

 

 1,481 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!