De-duping a table

From: Alan B <Alan.B_at_opcomp.demon.co.uk>
Date: Sat, 7 Apr 2001 09:37:40 +0100
Message-ID: <986633055.23980.0.nnrp-10.9e985e86@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 - 03:37:40 CDT