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


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