Eliminating Duplicate rows
Hi all What is the best way to eliminate duplicate rows. I have table with 5 million rows , I want to remove all the duplicate rows , What is the best method to do this, Informix 7.3 , HP-Ux thanks in adavance Suresh Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.
Hi,
Probably the most stupid way is :
unload .... select unique .... ;
drop table ....
'recreate table with e.g. dbschema'
dbload ....
I wish, I knew something better...
Peter
peddis@my-deja.com wrote:
> Hi all
>
> What is the best way to eliminate duplicate rows.
> I have table with 5 million rows , I want to
> remove all the duplicate rows , What is the
> best method to do this, Informix 7.3 , HP-Ux
>
> thanks in adavance
>
> Suresh
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
peddis@my-deja.com wrote:
>
> Hi all
>
> What is the best way to eliminate duplicate rows.
> I have table with 5 million rows , I want to
> remove all the duplicate rows , What is the
> best method to do this, Informix 7.3 , HP-Ux
>
> thanks in adavance
>
> Suresh
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
I suggest you try this,
on a small table first, before
checking out on the larger one...
say the table name is 'mybadtable' :
get exclusive lock on this table.
insert into temp t_dups
select a.f1,a.f2...a.fn
from mybadtable a, mybadtable b
where a.f1 = b.f1 and
b.f2 = b.f2
and a.rowid <> b.rowid ;
-- this should populate t_dups with the duplicate rows
delete from mybadtable
where mybadtable.f1 = t_dups.f1 and
mybadtable.f2 = t_dup.f2 etc...