| fitzjarrell@cox.net 2005-05-26, 7:23 am |
|
yeoen@sdu.nl wrote:
> This is my statement :
>
>
> update relpersoonhierarchie
k 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@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, relpersoonhierarchie
k rel
where fun.id = rel.id;
select fun.id
from fun_import fun, relpersoonhierarchie
k 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 relpersoonhierarchie
k 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
|