Re: deleting duplicate rows

carr_at_ist.flinders.edu.au (Matthew J Carr) writes:

>I have a table consisting of 3 columns: thread_id, mess_id, length
>I am trying to remove duplicate rows of thread_id and mess_id so only one copy remains
 

>Is it possible to construct a query using the rowid?
 

>e.g delete from th_table t1
> where rowid > (select rowid from th_table
> where thread_id = t1.thread_id
> and mess_id = t1.mess_id
> and rowid < t1.rowid)
 

>It returns an errror: ORA-01427: single-row subquery returns more than one row

>Can it be done this way????
>
Try this:
delete from th_table t1
where t1.rowid > (select min(t2.rowid) from th_table t2 where t2.thread_id = t1.thread_id and t2.mess_id =t1.mess_id)

Have fun.
Robert Rosales
Sonica Software Corp. Received on Fri Jul 29 1994 - 19:18:50 CEST