Re: ORA-01427 single-row subquery returns more than one row

yeoen_at_sdu.nl wrote:
> This is my statement :
>
>
> update relpersoonhierarchiek rel
> set rel.soortfunctie =
> (select fun.cat
> from fun_import fun
> where fun.id = rel.id)
> where rel.id =
> (select fun.id
> from fun_import fun
> where fun.id = rel.id)
>
> ORA-01427: single-row subquery returns more than one row
>
> Please help me. don't know what i'm doing wrong
> msg me on yeoen_at_sdu.nl please
> thank's in advance

The problem is obvious, really; your subqueries (modified slightly to run independently of the main query):

 select fun.cat
 from fun_import fun, relpersoonhierarchiek rel  where fun.id = rel.id;

 select fun.id
 from fun_import fun, relpersoonhierarchiek rel  where fun.id = rel.id;

return more than one row, and you would have discovereed that fact had you run the subqueries independently. Simply because you have coded:

 where fun.id = rel.id;

cannot guarantee a single-row result set unless there is a strict one-to-one relationship between the two tables. Obviously this condtion does not exist, and your equalities fail as there is more than one rvalue to compare or assign. Change your "=" to "IN" for second subquery and you'll fix part of the problem:

 update relpersoonhierarchiek rel
 set rel.soortfunctie =
 (select fun.cat
  from fun_import fun
  where fun.id = rel.id)
 where rel.id IN
 (select fun.id
  from fun_import fun
  where fun.id = rel.id);

The remaining subquery cannot be used to assign a value to a column as more than one value exists for a given fun.id, so the update will still fail after the changes have been made to the subquery in the WHERE clause. You seriously need to reconsider why you're coding this as you do and decide upon a better way to generate your assignment values. I state again you need to run these subqueries independently to discover what results they return. This would greatly decrease your frustration and the incidence of experiencing the error you now receive.

The error message has been telling you all along the nature of the problem. Simple logic should have revealed the underlying cause ('Where am I getting multiple rows? Hmmm, it must be the subqueries ...').

David FItzjarrell Received on Thu May 26 2005 - 07:30:59 CDT