Re: De-duping a table

I have found that it is quicker to build a table on your subquery and then run a delete command using this result set.

create table DUPES_TO_GO as
select min(ROWID) ROW_ID
from......;

delete from MY_TABLE
where rowid in
(select ROWID from DUPES_TO_GO);

same logic as yours, but quicker.

"Alan B" <Alan.B_at_opcomp.demon.co.uk> wrote in message news:986633055.23980.0.nnrp-10.9e985e86_at_news.demon.co.uk...
> Hi,
>
> I'm looking for a simple way to de-dupe a table based on primary key (I
> don't mind which dupe gets deleted).
>
> I've written some complex SQL which does an 8m row table in about 10 mins,
> but the following code which is much simpler disappears for hours.
>
> There are only a few duplicates in the table.
>
> delete from MY_TABLE
> where ROWID not in (select min(ROWID)
> from MY_TABLE
> group by MY_PRIMARY_KEY);
>
> Any suggestions would be appreciated.
>
> Regards,
> Alan
>
>
Received on Sat Apr 07 2001 - 08:46:38 CDT