Duplicate Data Delete

Posted in 2005

Prashant Shirude — Thu, 1 Sep 2005 11:14:43 -0400 — source: Usenet: comp.databases.informix

Does any one has a query to delete duplicate data? I need a query to delete duplicate data. --Prashant Database Analyst sending to informix-list

↪ replying to Prashant Shirude

Brice — 1 Sep 2005 16:21:11 -0700 — source: Usenet: comp.databases.informix

Alejandro has a good idea, but if the table is fragmented then the rowid is not guaranteed to be unique. Here is another thought in finding duplicates: SELECT <primary_key>, COUNT(*) FROM tablename GROUP BY <primary_key> HAVING COUNT(*) > 1 Hope this helps. Brice Avila Minneapolis, Minnesota

↪ replying to Brice

david@smooth1.co.uk — 1 Sep 2005 16:53:32 -0700 — source: Usenet: comp.databases.informix

make sure there is an index on the priamry key. SELECT MAX(<primary_key>) maxme, COUNT(*) cnt FROM tablename GROUP BY <primary_key> HAVING COUNT(*) > 1 INTO TEMP x WITH NO LOG; DELETE FROM tablename where primary key in (SELECT <maxme> FROM x);