I think that this will delete the records which do not have duplicates.
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:9an873$4bqek$3_at_ID-62141.news.dfncis.de...
>
> "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
> >
> >
>
> Two possible solutions
> - (using Oracle 7.3.3 or higher, and cost based optimiser)
> add the hash_aj hint to the subquery
> - (working in any version, any optimiser)
> delete from table x
> where x.rowid =
> (select min(y.rowid)
> from table y
> where y.key = x.key)
>
> Done the latter statement many times, and I'm sure it works.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
>
>
>
Received on Sat Apr 07 2001 - 11:36:26 CDT
