Re: Deletion of duplicate rows in a table

> > > Hi ! > > can somebody tell me how to delete duplicate rows from a table using a single > query in Informix. Is it possible in Ansi-Sql ? > > I tried using the following query but it is giving a syntax error : > > delete from table > where ( select column 1, column 2 > from table > group by column 1, column 2 > having count(*) > 1 )> > Appreciate your response > > - Kishore > > Hi Kishore ... Informix says Delete / Insert /Update cannot uses data taken from the same table in a sub query . It suggests to create a temp table first . By referencing the temp table main table rows can be deleted ... And group by caluse cannot be used with delete clause . Note : Check error number 360. I apologise for posting misleading information in the previous posts . select column 1, column 2 from table group by column 1, column 2 having count(*) > 1 into temp dup; delete from table where table.coulmn1 in ( select dup.column1 from dup where dup.column2 = table.column2 ); --- This works ...... Bye. Peter. \\|||/ ( O-O ) *-----------------*-----------*--------.ooo0--(_)-0ooo.--------*--------* Residence : Office : 2409 Otb Appartements Siemens Nixdorf Ottobrunner str 92 Informationsysteme AG 81737 Munich Office Automation Business Unit Germany Otto-Hahn-Ring 6 81739 Munich Germany Ph : 49-89-6800062409/ Ph : 49-89-4144-7114 49-89-6800060 Extn 2409 e-mail : mpeter.muc@sni.de .oooO ( ) Oooo. *-----------------*-----------*------------\\ (----( )---------*--------* \\_) ) / (_/