Home » SQL & PL/SQL » SQL & PL/SQL » "ORA-01427: single-row subquery returns more than one row"
"ORA-01427: single-row subquery returns more than one row" [message #241988] Thu, 31 May 2007 05:29 Go to next message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
Hi,

While executing below query i am getting "ORA-01427: single-row subquery returns more than one row"

update ga000ucc02 a
set fileno=(select fileno from ga000ucc04 b
where a.ucc3num = b.ucc3num and
(a.filetype = b.ucc3type2 or
(a.filetype is null and b.ucc3type2 is null))
and a.filedate = b.ucc3filedate)
where fileno is null
/

plz help me on the same....!!!

Thankx,
Rahul
Re: "ORA-01427: single-row subquery returns more than one row" [message #241998 is a reply to message #241988] Thu, 31 May 2007 05:46 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
try this...

update ga000ucc02 a
set fileno=(select DISTINCT fileno from ga000ucc04 b
where a.ucc3num = b.ucc3num and
(a.filetype = b.ucc3type2 or
(a.filetype is null and b.ucc3type2 is null))
and a.filedate = b.ucc3filedate)
where fileno is null

if it still gives the same error then your sub query is returning more than one FILENO. Check it individually in SQL.
Re: "ORA-01427: single-row subquery returns more than one row" [message #242002 is a reply to message #241998] Thu, 31 May 2007 05:57 Go to previous messageGo to next message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
Thankx for the reply...

i have executed the query as u have mentioned but still getting the same error...!!!

what i am trying to do is ga000ucc02 is having so many null fileno and i am trying to update those by referring ga000ucc04 table...i think ucc3num column which is there in ga000ucc02 and ga000ucc04 is having duplicate values...may be this is causing the update script...

i am trying few different ways....plz help me

[Updated on: Thu, 31 May 2007 06:10]

Report message to a moderator

Re: "ORA-01427: single-row subquery returns more than one row" [message #242010 is a reply to message #241988] Thu, 31 May 2007 06:22 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
ok try this...

update ga000ucc02 a
   set fileno =
        (select DISTINCT b.fileno
           from ga000ucc04 b
          where B.ucc3num = A.ucc3num 
            and NVL(a.filetype, '#') = NVL(b.ucc3type2, '#')
            and a.filedate = b.ucc3filedate)
 where fileno is null;



Now how you should check the data is run this qry in SQL and see if there are multiple values in b.fileno.
If there are... then there is no way on gods good earth that you can run the above statement. Unless, of course your remove the multiple values.

checking query:
  select ucc3num, NVL(ucc3type2, '#'), ucc3filedate, COUNT(DISTINCT fileno) filecount
    from ga000ucc04
   group by ucc3num, NVL(ucc3type2, '#'), ucc3filedate
  HAVING count(DISTINCT fileno) > 1;



it should NOT return any records.
If it does, your update query will not work for that combo.

ENJOY Cool
Re: "ORA-01427: single-row subquery returns more than one row" [message #242015 is a reply to message #241988] Thu, 31 May 2007 06:38 Go to previous messageGo to next message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
alright...!!!

let me try this.... i will let you know on this...!!!

[Updated on: Thu, 31 May 2007 06:39]

Report message to a moderator

Re: "ORA-01427: single-row subquery returns more than one row" [message #242021 is a reply to message #242015] Thu, 31 May 2007 07:05 Go to previous messageGo to next message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
sorry to say...its not working same error Sad

see below sql:-

update ga000ucc05 a
set fileno=(select fileno from ga000ucc04 b
where a.ucc3num = b.ucc3num and
(a.filetype = b.ucc3type2 or
(a.filetype is null and b.ucc3type2 is null))
and a.filedate = b.ucc3filedate and rownum=1)
where fileno is null
/

seems this will work for me...!!!

i have executed this and it went through....now i need to validate updated records...!!!

lets hope for the best Smile
Re: "ORA-01427: single-row subquery returns more than one row" [message #242025 is a reply to message #242021] Thu, 31 May 2007 07:28 Go to previous messageGo to next message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
No Message Body

[Updated on: Thu, 31 May 2007 08:12]

Report message to a moderator

Re: "ORA-01427: single-row subquery returns more than one row" [message #242042 is a reply to message #242025] Thu, 31 May 2007 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hmm what really stupid things were written in the preceding post!
Re: "ORA-01427: single-row subquery returns more than one row" [message #242088 is a reply to message #242042] Thu, 31 May 2007 10:08 Go to previous messageGo to next message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
sorry....!!! i have uploaded wrong information in that post...!!!

thats y i deleted...!!!

onwards i will take care of this
Re: "ORA-01427: single-row subquery returns more than one row" [message #242105 is a reply to message #242088] Thu, 31 May 2007 11:46 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't remove it.
You can report to the moderator to ask to delete your post.

Regards
Michel
Previous Topic: CLOB processing taking up large area in temp tablespace
Next Topic: select privilege problems
Goto Forum:
  


Current Time: Sat Dec 10 03:04:20 CST 2016

Total time taken to generate the page: 0.05985 seconds