Re: How to delete duplicate rows with sql
>
> To all sql experts:
> Could any body tell me how to delete duplicate rows in a table. I was
> able to list the duplicate rows in table like this:
> select rowid, a.*
> from a, b {a & b are aliases for sam table. basically a self join}
> where a.rowid != b.rowid;>
> I can't this select statement as subquery because we can not the update
> the table in a subquery. Any ideas ... please. BTW we have not yet
> installed 4gl yet.
>
> Thanks in advance.
>
Siva,
This is a quick version of what I have done in the past. Please check
it carefully as I don't have my old code with me. I assume you want to
save one of the duplicates and delete all the other duplicates.
{ select the duplicates }
select keyfield, count(*)
from table
group by keyfield haveing count(*) > 1
into temp A;
{ select rowids of duplicates }
select rowid, keyfield
from table
where keyfield in ( select keyfield from A)
into temp B;
{ save one of the duplicates so it does not get deleted }
select keyfield, min(rowid) save_rowid
from B
group by keyfield
into temp C;
{ delete the duplicates }
delete from table
where rowid in
( select rowid from B where rowid not in
( select save_rowid from C ));
Again, test this carefully and make a backup before you delete.
Regards - lester
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Grant group privileges for Informix databases with DB Privileges #
#############################################################################