Re: Eliminating duplicates

In article <1994Apr19.215930.1228_at_rat.csc.calpoly.edu>, mbhatt_at_galaxy.csc.calpoly.edu (Manish J. Bhatt) writes:
|> Is there a way to remove duplicate records from a table? I did an
|> import from a backup three times, and I didn't realize that each time
|> it would add in another copy of a record. I can't go back to the
|> backup because new data has been added since then.
|>
|> Thanks,
|>
|> Manish
|>
|> ---------------------------------------------------------------------------
|> Manish J. Bhatt Administrative Systems
|> AIX Systems Administrator California Polytechnic State University
|> mbhatt_at_dtvrut.infsys.calpoly.edu S a n L u i s O b i s p o
|>
Here's my old recipe:

create table dups (ids varchar(30) not null);

insert into dups
select min(rowid)
from theTable
group by <primary keys>
having rowcount > 1;

delete theTable where rowid in (select rowid from dups);

commit;

where <primary keys> are whatever columns you feel have duplicate values and <cname> is the name of one of the columns in theTable.

You need to do this more than once if there are lots of duplicates.

Alternatively, the logic can be turned around to put the first value of every match into the dups table and then delete all rows NOT IN this list.

Your mileage may vary. Enjoy. Received on Wed Apr 20 1994 - 22:31:00 CEST