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

Quote:

---

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)

---

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?

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)