Re: DELETING DUPLICATE ROWS

From: Greg Kainz <gkainz_at_rmi.net>
Date: 1997/05/29
Message-ID: <338E579B.31EC@rmi.net>#1/1

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

Received on Thu May 29 1997 - 00:00:00 CDT