Re: deleting duplicate rows

In article <1994Jul29.042203.59417_at_frodo.cc.flinders.edu.au> carr_at_ist.flinders.edu.au 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????
>
If you only had duplicates, I believe the above would have worked; however, if you have triplicates, quadruplicates(is this a word?), etc., it will fail as noted.

There may be other more efficient ways, but I think this will work:

	delete from th_table tt
	where rowid <> (select max(rowid)
	                from th_table
	                where thread_id = tt.thread_id
	                  and mess_id = tt.mess_id)

Good luck.

-- 
============================================================================
Michael Sallwasser  | Down one path is utter dispair and hopelessness. Down 
Northrop Grumman    | the other is total destruction. Let us choose wisely.