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

[Rajesh]

Hi all,

 

         delete from emp1 

         where sal=(select max(sal) from emp1 

                                                 group by sal

                                                  having count(empno||ename)>1);

I ran the above query and returns an error like this ORA-01427: single-row subquery returns more than one row 

 

Thank you in advanced. 

[Michael Moore]

change "=" to "in"

--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
 
---
You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Niraj Parihar]

Hi,
By looking at your sub query you should write delete from emp1 where sal in(your inner query).

Its fetching more than one row as you have grouped by sal and you should use in rather than =

[ddf]

You are specifically asking for multiple rows from emp given that there is more than one SAL value that meets that criteria:

 

 SQL> select max(sal) from emp group by sal having count(*) > 1;

 

  MAX(SAL)
----------
      1250
      3000

 

SQL>

 

Michael is correct -- you need to change the '=' to 'in':

 

SQL> delete from emp
  2  where sal=(select max(sal) from emp
  3  group by sal
  4  having count(empno||ename)>1);
where sal=(select max(sal) from emp
           *
ERROR at line 2:

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

SQL>

 

SQL> delete from emp
  2  where sal in (select max(sal) from emp
  3  group by sal
  4  having count(empno||ename)>1);

 

4 rows deleted.

 

SQL>

 

 

David Fitzjarrell