Re: De-duping a table

The delete statement that has the where rowid > (select min(rowid)

Will work , But I have found it easier to create a new table as follows

create table table_name_dedup
nologging...(ect)
as select * from table_name
where rowid in (select min(rowid) from table_name);

then check the results and makesure the are no dupes in the de-dup table the drop table with dupes and rename the dedupe table

"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 Sun Apr 15 2001 - 16:29:55 CDT