This error:
ORA-01427: single-row subquery returns more than one row
Means that the sub-query really is finding more than one record that
matches the "where" clause criteria of the sub-query.

You have two options:
1. Modify the "where" clause of the sub-query to make sure it returns
only one record
2. Add a group operator (like: "min" or "max") on the selected column:
purchase_line_detail_descr. But, then you may not get exactly what you
expect, unless you know that all of the matching records have the same
value.

-----Original Message-----
From: Jim Thomas
[mailto:oracledba-ezmlmshield-x19079405.[Email address protected]
Sent: Monday, April 28, 2008 3:38 PM
To: LazyDBA Discussion
Subject: RE: Updating Table using select/ ORA-01427: single-row subquery
returns more than one row error

I attempted a manual update using only 1 po_number and one
po_line_number
I still got the 'subquery returns more then one row error'

So I did a small query on both the po_number and po_line_number
and all was returned was the count of 1

Could this be due to the field I am attempting to update is a
VARCHAR2(4000)
and it has more than one line in the description field-

I'm grasping at straws here- but can we update a VARCHAR2(4000) without
any special operators?

Is there a way to bypass this error?

Thank You - Jim T
see below for queries

SMALL UPDATE --------------------
1 update jpt_pop_lines j
2 set purchase_line_detail_descr=
3 (select purchase_line_detail_descr
4 from temp_pop_lines t
5 where po_number='0100196'
6* and po_line_number='1')
SQL> /
(select purchase_line_detail_descr
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

SMALL RECORD QUERY ----------------
SQL> select count(*)
2 from jpt_pop_lines
3 where po_number='0100196'
4 and po_line_number='1'
5 ;
1

thanks again to all those who have taken the time to help me with this
very difficult issue

regards
Jim T


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




THE INFORMATION CONTAINED IN THIS E-MAIL MESSAGE AND ANY ATTACHMENTS SENT FROM GENTEX CORPORATION IS GENTEX CONFIDENTIAL INFORMATION INTENDED ONLY FOR THE PERSONAL USE OF THE INDIVIDUAL OR ENTITY NAMED ABOVE. If you are not the intended recipient, you are hereby notified that any review, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail, and delete this e-mail message and any attachments from your computer. [gntx v.1]

Oracle LazyDBA home page