This may just be to simplistic, but I solve my duplicate record problems
with a unique constraint. This way when your inserting records, the first
unique record will get inserted but not the duplicates. So when you are
done transferring 100,000 records it will say something like 100,000
transferred, 67,893 success, 32,107 errors.
The errors are your duplicate records which never made it into the table.



-----Original Message-----
From: alan davies [mailto:Alan.Davies@...]
Sent: Tuesday, June 29, 2004 10:48 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] removing duplicate records


Hi all
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

I have tried various combinations
insert into weighcopy
select distinct(weigh_code) from weighall
This gives the correct number of records and the correct weigh_code. But
when I try to update I get an error - multiple rows in singleton select -
which is quite right and expected, as follows:
update weighcopy
set name=(select namefrom weighall
where weighall.weigh_code = weighcopy.weigh_code )
If I try to qualify with say a MIN
update weighcopy
set name=(select min(name) from weighall
where weighall.weigh_code=weighcopy.weigh_code)
I get this error
Unsuccessful execution caused by system error that does not preclude
successful execution of subsequent statements.
lock conflict on no wait transaction.
deadlock.
update conflicts with concurrent update.

How do I perform this update, or else how do I delete the duplicate records
from the main table?
Any help gratefully received.


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






Yahoo! Groups Links






[Non-text portions of this message have been removed]