Re: Deletion of duplicate rows in a table
The best way to get rid of EXACT duplicates (i.e. all columns in the table
contain duplicate values) is to do something like
unload to filename
select UNIQUE *
from tablename;
delete from tablename; {Might want to drop the table and thenrebuild if it
is very big. It's quicker to
drop than delete.}
load from filename
insert into tablename;
I'd try it from inside a transaction with a rollback work first or at
least take do the unload to see what you get before you do the delete.
That only works for exact duplicates though.
Here's a nice way to identify duplicates where only the key fields are
duplicated:
select column1, column2, column3, count(*)
from tablename
having count(*) > 1
You can then do another select for each row returned to get the rowids and
then pick one to delete.
Hope this helps some.
--- Frank