This content has been marked as final. Show 19 replies
don123 wrote:You've got more than one row in twyinfo that matches the id from twypoly. You need to make sure that
hi, please help me to resolve this, working with oracle 11g.
update twypoly t1 set t1.twyindex=(select twyindex from twyinfo t2 where t2.id=t1.id);
the error message is:
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
the subquery returns only one row, perhaps by being more restrictive in your conditions.
hi, please see the data
twyindex column in twypoly (table1) should be updated with 3197 value by selecting from twyinfo (table2) for Z
select twyindex, id from twypoly;
select twyindex, id from twyinfo;
don123 wrote:Well, there we are then. You have duplicates for a particular id.
i have provided sample data as there are hundreds of rows.
can you provide the SQL ?
The SQL I used was exactly what you posted: the problem isn't the SQL, it's your data.
What is the output of
select id ,count(*) from twyinfo having count(*) > 1 group by id;
don123 wrote:I don't know your data or the business reason why there are duplicates. It may be there shouldn't be and you need to clean up the data.
hi, please see the output, as you said, there are duplicates, how to resolve this ? any solution ?
SQL> select id, count(*) from twyinfo having count(*) > 1 group by id;
6 rows selected.
For each duplicate, are values of the twyindex columns the same?