Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01427: single row subquery returns more than one row  () 1 Vote
ORA-01427: single row subquery returns more than one row [message #320708] Fri, 16 May 2008 01:08 Go to next message
umaramuddasser
Messages: 11
Registered: May 2008
Location: Pakistan
Junior Member
I am getting this error msg. My query is:

update stud_course_reg set stu_status = 0 where stu_status = (
select a.stu_status from stud_course_reg a, stu_fee_reg b where a.vhno = b.vhno and b.semester = 3 and b.sem_code = 'BEE-FALL-07' and b.remarks = 'Not Promoted');
Re: ORA-01427: single row subquery returns more than one row [message #320709 is a reply to message #320708] Fri, 16 May 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your subquery returns more than 1 row.
If it is expected use IN instead of =

Regards
Michel
Re: ORA-01427: single row subquery returns more than one row [message #320710 is a reply to message #320709] Fri, 16 May 2008 01:13 Go to previous messageGo to next message
umaramuddasser
Messages: 11
Registered: May 2008
Location: Pakistan
Junior Member
I know my subquery is returning more than one rows but when I use IN it updates all the records in that table(about 43000) but I need to update only 228 records.
Re: ORA-01427: single row subquery returns more than one row [message #320711 is a reply to message #320710] Fri, 16 May 2008 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So your query is wrong and we can't know why, this is YOUR data and YOUR requirements, only YOU know them.

Regards
Michel
Re: ORA-01427: single row subquery returns more than one row [message #320714 is a reply to message #320711] Fri, 16 May 2008 01:20 Go to previous messageGo to next message
umaramuddasser
Messages: 11
Registered: May 2008
Location: Pakistan
Junior Member
There is no problem with the subquery when I run it.It returns me 228 records. It only causes the problem when I run it with the update query.
Re: ORA-01427: single row subquery returns more than one row [message #320717 is a reply to message #320714] Fri, 16 May 2008 01:24 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What does
select count(distinct stu_status) from stud_course_reg
return?
Re: ORA-01427: single row subquery returns more than one row [message #320718 is a reply to message #320717] Fri, 16 May 2008 01:28 Go to previous messageGo to next message
umaramuddasser
Messages: 11
Registered: May 2008
Location: Pakistan
Junior Member
It returns '2'.
stu_status can have only two values either 0 or 1.
Re: ORA-01427: single row subquery returns more than one row [message #320719 is a reply to message #320718] Fri, 16 May 2008 01:34 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Then take that information, look at your query again and think about it,
you have basically run
update stud_course_reg set stu_status = 0 where stu_status IN (1, 0)

How is that identify only 228 rows? You need to identify those (228) rows explicitly, using their VERY non-unique value of status is useless.
Re: ORA-01427: single row subquery returns more than one row [message #320720 is a reply to message #320718] Fri, 16 May 2008 01:34 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that WHERE clause of the UPDATE statement isn't enough - it requires additional condition(s) in order to restrict record set which is to be updated.
Re: ORA-01427: single row subquery returns more than one row [message #320745 is a reply to message #320719] Fri, 16 May 2008 03:20 Go to previous messageGo to next message
umaramuddasser
Messages: 11
Registered: May 2008
Location: Pakistan
Junior Member
Thank you so much, I got it and have solved the problem.
Re: ORA-01427: single row subquery returns more than one row [message #320746 is a reply to message #320720] Fri, 16 May 2008 03:21 Go to previous message
umaramuddasser
Messages: 11
Registered: May 2008
Location: Pakistan
Junior Member
Thank you for your identification. I am done with it.
Previous Topic: emp table
Next Topic: Bulk insert
Goto Forum:
  


Current Time: Sun Dec 04 00:12:08 CST 2016

Total time taken to generate the page: 0.07208 seconds