Arno - Thanks for the advice, but this does not work. I get the sql
hourglass and windows task manager shows cpu at 100% with ibexpert not
responding. All I can do is end the task (this is the same on another
machine trying ibconsole) because the system is unresponsive. At 08:07
30/06/2004, you wrote:

>Hi,
>
> > Can anyone help with removing a lot of duplicate records that have been
> > imported from an external system.
> > System is FB 1.5, W2K, IBExpert or Ibconsole
> > Basically I have done this before by this sort of process
> > select supp_code, count(supp_code)
> > from supplier
> > group by supp_code
> > having count(supp_code)>1
> > This gives me the duplicates and they have been corrected manually.
> > In this instance, however, I have some 210,000 records in total, but only
> > some 52,000 distinct records. ( There has been an import of data 4 times
> > not just once) I need to remove the duplicates (or is that
>quadruplicates?)
> > For clarity the table is called weighall and I have cloned it to a table
> > called weighcopy. I have added a record_id and updated it with a trigger
>to
> > get a unique record number, but it does not help me yet.
> > Fields are Weigh_Code integer, record_id integer, Name char(30) (plus
>others)
> > Data is typically
> > weigh_code Record_id Name
> > 1 1 Record1
> > 1 2 Record1
> > 2 3 Record2
> > 2 4 Record2 ................ and so on
>
>This should delete all duplicates :
>
>DELETE FROM Supplier s1
>WHERE
> NOT SINGULAR(SELECT * FROM Supplier s2
> WHERE s2.Weigh_Code = s1.Weigh_Code)
>
>Regards,
>Arno Brinkman
>ABVisie
>
>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>Firebird open source database (based on IB-OE) with many SQL-99 features :
> http://www.firebirdsql.org
> http://www.firebirdsql.info
> http://www.fingerbird.de/
> http://www.comunidade-firebird.org/
>
>Support list for Interbase and Firebird users :
>firebird-support@yahoogroups.com
>
>Nederlandse firebird nieuwsgroep :
>news://newsgroups.firebirdsql.info
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>--
>Incoming mail is certified Virus Free.
>Checked by AVG Anti-Virus (http://www.grisoft.com).
>Version: 7.0.253 / Virus Database: 263.3.7 - Release Date: 27/06/2004

Alan J Davies
Aldis
Tel: +44(0)1926 842069
Fax: +44(0)1926 843755


--
Outgoing mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.253 / Virus Database: 263.3.7 - Release Date: 27/06/2004