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