Re: DELETING DUPLICATE ROWS

Greg Kainz wrote:
>
> Shiva K Mantri (CS) wrote:
> >
> > Hi Everybody,
> >
> > Can any one tell me how to delete duplicate rows in a table?
> >
> > 1) the rows are duplicate by primary key.
> >
> > I need to delete all the rows identical row values
> >
> > ------Shiva Mantri
>
> delete from table
> where (rowid, pkey) in
> ((select rowid, pkey -- get the set of all records
> from table
> minus -- subtract the unique records from the set
> of -- all records
> select max(rowid), pkey -- max doesn't do anything because these
> are -- unique records but is needed for group
> by
> from table
> group by pkey
> having count(*) = 1)
> minus -- subtract one row for each of the replicated
> -- records from the set above
> select max(rowid), pkey
> from table
> group by pkey
> having count(*) > 1)
>
>
> --------------------------------------------------------------
> DataWeb Consulting Services
> "Web-enabling your databases today"
> voice:(303) 517-3863 FAX: (303) 422-5107
> gkainz_at_rmi.net
> --------------------------------------------------------------

Gosh, That seems awfully complicated. How about:

delete from table t1 where exists (select null from table t2

    where t2.pkey = t1.pkey and r2.rowid < t1.rowid);

Hope this helps

Ken Denny
kdenny_at_interpath.com Received on Fri May 30 1997 - 00:00:00 CDT