Re: Duplicate records

comments inline...

"Greg Kainz" <gregkainz_at_home.com> wrote in message news:AjHc6.123605$ge4.43942039_at_news2.rdc2.tx.home.com...
> or "... where rowid > ... select (min) ... " to save the lowest rowid in
 the
> group,
> or "where rowid < ... select (max)... " to save the highest rowid...
>

think carefully about this... a delete like this would delete all of the duplicates, and also delete a lot of rows that are not duplicates. maybe you intended to add some criteria to the where clause ?

> <derf23456_at_my-deja.com> wrote in message
 news:94s03g$ltg$1_at_nnrp1.deja.com...
> > Isn't this only going to work where there are two (and only two)
> > records that are duplicates? (It threre are 3 records the same, the
> > min will only choose one record to delete, leaving two that are the
> > same).

yes, you are correct. the proposed delete would delete only one of the duplicate rows. a subsequent execution of the same statement would also do the same thing. this could be repeated until no duplicates remain.

> >
> > If I'm correct above, I think changing the 'where rowid in' to 'where
> > rowid NOT in' would delete all the duplicates.

yep. this would delete ALL the duplicates, and also delete a lot of non-duplicates as well. be careful !

> > > delete from table_name
> > > where rowid in
> > > (select min(rowid) from table_name
> > > group by col1, col2, coln
> > > having count(0) > 1)
> > >

this query deletes ONLY duplicates, which is what the original poster asked for. Received on Sun Jan 28 2001 - 23:43:42 CST