Deletion of duplicate rows in a table

[Kishore Kaseebhotla]

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

[mpeter]

Hi Kishore ,

In the above query , u have not specified the condition for a

particular field . If u change the query as follws it should work ...

delete from table
where 1 != ( select count(*)

from table
group by column 1, column 2
having count(*) > 1 )

Note : having clause is not needed here .

..... Have a nice time ....

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 : mpete...@sni.de .oooO
( ) Oooo.
*-----------------*-----------*------------\ (----( )---------*--------*
\_) ) /
(_/

[mpeter]

}
}
} 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 ,

I have missed something in the above post ...

Extremely sorry ...

} delete from table

} where ( select column 1, column 2
} from table
} group by column 1, column 2
} having count(*) > 1 )

U have to link the main query and the sub query ......

I will post it later ...

Bye.
Peter.
mpete...@sni.de

[mpeter]

>
>
> 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 ......

[Susi...@united#u#group.ccmail.compuserve.com]

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:nkk...@pbdap.snfc666.PacBell.COM at CSERVE
>Date: 6/20/96 1:43 AM
>
>
>Sender: informix-...@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: nkk...@pbdap.snfc666.PacBell.COM (Kishore Kaseebhotla)
>Message-Id: <4q9i7r$s...@gw.PacBell.COM>
>Subject: Deletion of duplicate rows in a table
>Date: 19 Jun 1996 18:51:39 GMT
>Reply-To: nkk...@pbdap.snfc666.PacBell.COM (Kishore Kaseebhotla)
>Organization: Pacific * Bell
>Sender: informix-...@rmy.emory.edu
>To: inform...@rmy.emory.edu
>X-Informix-List-To: susik_lee@united#u#group.ccmail.compuserve.com
>X-Informix-List-Id: <news.25171>

[cb]

If you're not going to use a correlated sub query try the folling:
select x.rowid
from same-table x, same-table y
where x.rowid <> y.rowid
and x.unique-col = y.unique-col
and x.unique-col2 = y.unique-col2
.

You can retreive all the rowids that are duplicated and then
delete those rowids or unload first and then delete.

[R M Sams]

Susik_Lee@United#u#Group.CCMAIL.CompuServe.COM posted :

>> echo "unload to junk.unl select * from table" | isql database | sort |
>> uniq > junk.load;

>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 love the uniq command. Nowadays I use 'sort -u' which sorts and
'unique's also.

-Sams

[Frank660]

The best way to get rid of EXACT duplicates (i.e. all columns in the table
contain duplicate values) is to do something like

unload to filename
select UNIQUE *
from tablename;
delete from tablename; {Might want to drop the table and then
rebuild if it
is very big. It's quicker to
drop than delete.}
load from filename
insert into tablename;

I'd try it from inside a transaction with a rollback work first or at
least take do the unload to see what you get before you do the delete.

That only works for exact duplicates though.

Here's a nice way to identify duplicates where only the key fields are
duplicated:

select column1, column2, column3, count(*)
from tablename
having count(*) > 1

You can then do another select for each row returned to get the rowids and
then pick one to delete.

Hope this helps some.

--- Frank