Re: Deletion of duplicate rows in a table
This is not what you looking for, but have you thought about using
unix uniq command?
echo "unload to junk.unl select * from table" | isql database | sort |
uniq > junk.load;
echo "drop table table" | isql database;
echo "load from junk.load insert into table" | isql database;
Susik
Susik_Lee@united#u#group.ccmail.compuserv.com
______________________________ Reply Separator _________________________________
>Subject: Deletion of duplicate rows in a table
>Author: INTERNET:nkkasee@pbdap.snfc666.PacBell.COM at CSERVE
>Date: 6/20/96 1:43 AM
>
>
>Sender: informix-list-owner@rmy.emory.edu
>Received: from rmy.rmy.emory.edu (rmy.rmy.emory.edu [170.140.97.4]) by
>arl-img-5.compuserve.com (8.6.10/5.950515)
> id CAA11952; Thu, 20 Jun 1996 02:39:01 -0400
>Received: (from ilist@localhost) by rmy.rmy.emory.edu (8.7.1/8.7.1) id
>CAA13964 for susik_lee@united#u#group.ccmail.compuserve.com; Thu, 20 Jun 1996
>02:39:22 -0400 (EDT)
>From: nkkasee@pbdap.snfc666.PacBell.COM (Kishore Kaseebhotla)
>Message-Id: <4q9i7r$slm@gw.PacBell.COM>
>Subject: Deletion of duplicate rows in a table
>Date: 19 Jun 1996 18:51:39 GMT
>Reply-To: nkkasee@pbdap.snfc666.PacBell.COM (Kishore Kaseebhotla)
>Organization: Pacific * Bell
>Sender: informix-list-owner@rmy.emory.edu
>To: informix-list@rmy.emory.edu
>X-Informix-List-To: susik_lee@united#u#group.ccmail.compuserve.com
>X-Informix-List-Id: <news.25171>
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