Try to empty table and after that create unique index.Later pump data and
commit.
I think that was proposed.

Boguslaw Brandys

> Hi Ed
> Thanks for the prompt reply. I must be missing something here because
> that
> does indeed sound simple. It doesn't work for me though. I tried adding a
> unique PK and also, separately, a unique index, both on the main field -
> weigh_code. Both versions come up with the same error - violation of
> primary or unique key constraint. Is there a switch or an option to turn
> off error-checking?
> I used
> insert into weighcopy
> select * from weighall
> Regards
> Alan
>
> At 19:11 29/06/2004, you wrote:
>
>> 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]
>>
>>
>>
>>
>>
>> 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
>
>

--
Best Regards
Bogusław Brandys