-
Help: ORA-01427- single-row subquery returns more than one row
I have two tables with two similar fields point_value and id. I am trying to update table_tmp based on values from table_assn but keep getting error message ORA-01427: single-row subquery returns more than one row. I will appreciate any help on getting the update accomplished.
update table_tmp a
set a.point_value = (select b.point_value
from table_assn b,
where b.id = a.id
and b.point_value != a.point_value)
OCP 8i DBA
I'm still learning
-
The condition ...
Code:
where b.id = a.id
and b.point_value != a.point_value
... is not enough to ensure that you only get one row back from table_assn for each row in table_tmp.
-
Thanks for the prompt response. You're right, that's what is causing the error message. Any suggestion on how I can fix this?
OCP 8i DBA
I'm still learning
-
Thanks guys, I was able to resolve it. Here is my query:
I created a dummy table
create table test_dummy
as (select b.point_value
from table_assn b,
where b.id = a.id
and b.point_value != a.point_value)
then
update table_tmp
set point_value = ( select point_value from table_assn where table_assn.id = table_tmp.id )
where EXISTS ( select point_value from table_assn where table_assn.id = table_tmp.id)
OCP 8i DBA
I'm still learning
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|