Re: Eliminate Duplicate rows from table
From: peddis@my-deja.com
>
>what is the best way to eliminate duplicate rows from a table in
>Informix 7.3. Table has 5 Million rows .
select <what I want ot be unique>, max(rowid) keepit
from table
into temp eric with no log;
delete from table
where rowid not in (select keepit from eric);
Not necessarily the fastest way but at least you don't need to drop the real table. The use of eric is not compulsory :-))
Paul Watson
WF Software Ltd
Tel +44 1436 674729
Fax +44 1436 678729
www.wfsoftware.co.uk
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
Hi Paul,
> From: peddis@my-deja.com
> >
> >what is the best way to eliminate duplicate rows from a table in
> >Informix 7.3. Table has 5 Million rows .
>
> select <what I want ot be unique>, max(rowid) keepit
> from table
> into temp eric with no log;
You have forgotten the group by .
select <what I want to be unique>, max(rowid) keepit
from table group by 1
into temp eric with no log;
>
> delete from table
> where rowid not in (select keepit from eric);>
> Not necessarily the fastest way but at least you don't need to drop the real table. The use of eric is not compulsory :-))
>
> Paul Watson
> WF Software Ltd
> Tel +44 1436 674729
> Fax +44 1436 678729
> www.wfsoftware.co.uk
>
> **********************************************************************
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
>
> This footnote also confirms that this email message has been swept by
> MIMEsweeper for the presence of computer viruses.
>
> www.mimesweeper.com
> **********************************************************************
Make sure this is not done on a fragmented table -- if the table is fragmented across chunks the rowid may not be unique to the
table -- only to the dbspace.
Tom
Dirk Niemeier wrote:
> Hi Paul,
>
> > From: peddis@my-deja.com
> > >
> > >what is the best way to eliminate duplicate rows from a table in
> > >Informix 7.3. Table has 5 Million rows .
> >
> > select <what I want ot be unique>, max(rowid) keepit
> > from table
> > into temp eric with no log;
>
> You have forgotten the group by .
>
> select <what I want to be unique>, max(rowid) keepit
> from table group by 1
> into temp eric with no log;
>
> >
> > delete from table
> > where rowid not in (select keepit from eric);> >
> > Not necessarily the fastest way but at least you don't need to drop the real table. The use of eric is not compulsory :-))
> >
> > Paul Watson
> > WF Software Ltd
> > Tel +44 1436 674729
> > Fax +44 1436 678729
> > www.wfsoftware.co.uk
> >
> > **********************************************************************
> > This email and any files transmitted with it are confidential and
> > intended solely for the use of the individual or entity to whom they
> > are addressed. If you have received this email in error please notify
> > the system manager.
> >
> > This footnote also confirms that this email message has been swept by
> > MIMEsweeper for the presence of computer viruses.
> >
> > www.mimesweeper.com
> > **********************************************************************