Re: (Fwd) Re: delete duplicate rows, how???

Hey! Renee, ;-] ;-} ;-] I tell that was a good joke. I am :-x, :-&. I wish Informix-SQL or for that matter any SQL could provide such a syntax. I am aware of only the following syntax: delete from <table/view/synonym> [where <condition>/ current of cursor_name] may be with some variations in other databases. And I hope "that" delete statement doesn't work. Vijay R. Anisetti -- Jai boolo Ganesh maharaj ki, Jai! ---------- } From: Renee Speight <rspeight@skyserv4.med.osd.mil> } To: rambabu@sprynet.com } Subject: (Fwd) Re: delete duplicate rows, how??? } Date: Thursday, June 12, 1997 5:38 PM } } Hi Vijay, } } Try this } } delete col1, col2 } from tabname } where col1 = 'some value' } group by 1,2 } having count (*) > 1; } } I hope this works. } } Bye, } Renee } } --- Forwarded mail from "Vijay R. Anisetti" <rambabu@sprynet.com> } } Date: Thu, 12 Jun 1997 14:21:42 -0400 } From: "Vijay R. Anisetti" <rambabu@sprynet.com> } To: Informix mailing list <informix-list@rmy.emory.edu> } Subject: Re: delete duplicate rows, how??? } Reply-To: "Vijay R. Anisetti" <rambabu@sprynet.com> } } select distinct * from dupl_table into temp temp_table; } delete from dupl_table where rowid != -1; -- to delete without prompt } ;-) } insert into dupl_table select * from temp_table; } } This should work in an acceptable manner, provided the table is not } HUGE. } } Vijay R. Anisetti } } } } } Peter Lancashire wrote: } } } } Ramki Natesan wrote: } } > } } > Hellow friends, } } > Is there a way to delete all the duplicate rows in a given table with } } > simple sql statement in Informix? } } > } } > /Ramki. } } Version 7.1 will do it. Add an index with the set violations table on } } and it will tell you which the problem rows are. } } } } If the table is not too big you could unload it and use the Unix sort } } and uniq commands to remove duplicates. } } } } Relational database theory demands that this task is difficult. It } } should be impossible but for the sneaky rowids. } } -- } } Peter Lancashire } } Mail: Peter.Lancashire.PL1 at bayer.co.uk } } Information Systems Specialist, Bayer plc } } Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK } } Tel: +44-1635-562258, Fax: +44-1635-562281 } } ---End of forwarded mail from "Vijay R. Anisetti" <rambabu@sprynet.com> } } -- } Renee Speight Internet : Renee.Speight@med.osd.mil } EDS-D/SIDDOMS Phone : (703) 845-3522 } Skyline Four, Suite 800 } 5113 Leesburg Pike } Falls Church, VA 22041