Good day
=-00-0=-=-0

we tried this, but it also came back with no results ? We also created a clean table and pumped all the data
to the clean table and then tried it on that table and it still came back with nothing.

This is very strange.

Thanks once again for your help

Johannes

At 10:22 AM 03/02/2006, you wrote:

>When using old versions, the best solutions may be in old documents.
>Your particular question is brilliantly answered in Claudios classic
>article: "The mystery of rdb$db_key"
>(http://www.cvalde.net/document/mysteriousDbKey.htm). In short, you
>should do:
>
>delete from a a1
>where a1.rdb$db_key in (
>select a2.rdb$db_key
>from a a2 join a a3 on a2.code=a3.code
>and a2.rdb$db_key>A3.rdb$db_key)
>
>I never thought I would recommend using IN (<subselect>), I always
>prefer EXISTS. Though Claudio know what he is talking about and it's a
>very long time since I used InterBase 5.6, so you better stick to what
>is known to be working.
>
>Set
>
>--- In firebird-support@yahoogroups.com, Johannes Pretorius wrote:
>> Good day all
>>
>> We have a problem with a database of ours. It is in interbase 5.6.
>>
>> We have a table, for this example called A :
>>
>>
>> CREATE TABLE A (
>> CODE VARCHAR(6) NOT NULL
>> TITLE T_TITLE,
>> INITIALS T_INITIALS,
>> SURNAME LASTNAME
>> )
>>
>>
>> It has an index on the one column as example
>>
>> CREATE ASCENDING INDEX RD_CODE ON A (CODE)
>>
>> Now after a few years we have decided to do what we where suppose to
>> do from the beginning and that is to ADD a primary key on the table
>> on the CODE column
>>
>> We do this as follows :
>>
>> ALTER TABLE A
>> ADD CONSTRAINT A_PK1
>> PRIMARY KEY (CODE)
>>
>> We then got the error :
>>
>> KEY VIOLATION ATTEMPT TO STORE DUPLICATE VALUE (VISIBLE TO ACTIVE
>> TRANSACTIONS) IN UNIQUE INDEX "RDB$PRIMARY58"
>>
>> We then deleted all duplicates as follows
>>
>> delete from a a1 where (select count(code) from a a2 where a1.code =
>> a2.code)>1
>>
>> We then STILL got the same error. So when then Sweeped the database
>> and checked Forced Writes is ON. And dit it again but still the SAME
>> error.
>>
>> We don't really know anymore from here on what to do. If we
>> run the following query we get NO records that indicate to be
>> duplicated.
>>
>> select * from a a1 where (select count(code) from a a2 where a1.code
>> = a2.code)>1
>>
>>
>> If anybody can give us some ideas it will be appreciated.
>
>
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>