Home » SQL & PL/SQL » SQL & PL/SQL » Update error (10.2.0.3,Windows)
Update error [message #414146] Mon, 20 July 2009 09:15 Go to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
Hi all,

I'm trying to update table 'series' with ser_no from a temp table.. I'm getting this error now, not sure what I'm doing wrong..


SQL>update series s
	set ser_no =
		  (select ser_no from temp t
		   where t.v_no   = s.d_no
		   and   t.v_date = s.d_date );

ERROR at line 3:
ORA-01427: single-row subquery returns more than one row



Table series has 90000 rows..

Please give me your suggestions. Thanks

[Updated on: Mon, 20 July 2009 09:19]

Report message to a moderator

Re: Update error [message #414147 is a reply to message #414146] Mon, 20 July 2009 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=oracle+ORA-01427:+single-row+subquery+returns+more+than+one+row

This is a FAQ!
Re: Update error [message #414149 is a reply to message #414146] Mon, 20 July 2009 09:26 Go to previous messageGo to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
I used distinct ser_no. It worked
Re: Update error [message #414188 is a reply to message #414146] Mon, 20 July 2009 16:13 Go to previous messageGo to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
I've having another problem,

if values for s.d_no are 6 digits then t.v_no has only 5 digits.. if s.d_no has 5 digits then t.v_no has only 4 digits in the database..

SQL>update series s
	set s.ser_no =
		  (select t.ser_no from temp t
		   where t.v_no   = s.d_no
		   and   t.v_date = s.d_date
                                   and rownum=1);

SQL>select d_no from series;

d_no
-------

97891
95432
901348
87239

SQL> select v_no from temp;

v_no
------

9789
9543
90134
8723



I tried the select query below:

SQL>SELECT RTRIM('97891 ',' ') op FROM DUAL;

OP
-----
97891

But not sure if I can trim one digit at the end. Is it possible to trail one digit at the end of t.v_no in the update query?
THank you
Re: Update error [message #414190 is a reply to message #414146] Mon, 20 July 2009 17:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Why does this look like a classic homework problem and not a legitimate business problem?
Re: Update error [message #414191 is a reply to message #414146] Mon, 20 July 2009 17:46 Go to previous messageGo to next message
pleasehelp
Messages: 21
Registered: January 2007
Junior Member
Blackswan,

No. Its not home work.

I tried trunc(), it works now. For anyone facing similar problem:

update series s
set s.ser_no =
(select t.ser_no from temp t
where trunc(t.v_no /10) = s.d_no
and t.v_date = s.d_date
and rownum=1);

Re: Update error [message #414196 is a reply to message #414146] Mon, 20 July 2009 19:03 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>For anyone facing similar problem:
What business solution involves setting every s.ser_no to single value?

Last one in (into s.ser_no), wins.
Previous Topic: how to add null to a unique key
Next Topic: triggers
Goto Forum:
  


Current Time: Sun Dec 11 06:26:16 CST 2016

Total time taken to generate the page: 0.13391 seconds