 |
|
ORA-01427: single-row subquery returns more than one row tips
Oracle Error Tips by Burleson Consulting (S. Karam)
|
The Oracle docs note this on the
ora-01427 error*:
- ORA-01427
single-row subquery returns more than one row
- Cause:
The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to
specify values to compare because the subquery returned more than one
row.
-
- Action:
Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword
the query so only one row is retrieved.
On
Oracle Server and PL/SQL User Forum, Adam Wilbur received an ORA-01427 error
code and received this information:
Question
Posted by 'Adam Wilbur' on April 21, 2001 at 20:37:00 EST:
Here's what I am trying to do...
UPDATE TABLEA SET TABLEA.COLUMN1 =
(select TABLEB.COLUMN1 FROM TABLEA, TABLEB
WHERE TABLEB.COLUMN2 = 'String Value'
AND TABLEA.COLUMN3 = TABLEB.COLUMN3
AND TABLEA.COLUIMN4 = TABLEB.COLUMN4
AND TABLEB.VALUENAME = 'String Value');
This query returns multiple rows, all of which I need to update in TABLEA.
How do I do this without getting ORA-01427 error?
Answer
Dmytro offered this advice to resolve ORA-01427:
Posted by 'Dmytro' on April 23,
2001 at 02:48:59 EST:
Try to add and rownum=1 to your subquery conditions if you DO NOT care
about the value from the list or DO sure that they are the same.
Helpful Links:
You may want to refer to these helpful links concerning
single and multi row subqueries, and tips on
deleting
rows while avoiding ORA-01427 by Don Burleson.
