Re: Duplicate records

In article <9q7d6.1466$tW4.172316_at_nnrp2.sbc.net>,   "Spencer" <spencerp_at_swbell.net> wrote:
> 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 !
>

Oops! Good catch - thanks. And thank goodness for 'rollback;'

> > > > 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.
>
>

Sent via Deja.com
http://www.deja.com/ Received on Mon Jan 29 2001 - 08:12:25 CST