Try this instead:

select * from a a2
where a2.code IN
(select a1.code from a a1 group by a1.code having count(*) > 1)


--
Nick
-----We Solve your Computer Problems---
Panther, Ingres, UNIX, Interbase, Firebird - Available Shortly

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Johannes Pretorius
Sent: 03 February 2006 07:15
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Funny problem with creation of Primary keys on
table
Importance: High


Good day all
-=0-0=0-0-=-=0

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.

Yours Sincerely

Johannes Pretorius
(Programeerings Departement- Promed)

Tel : +27 11 607-3300
Faks : +27 11 622-6939
webtuiste : http://www.promed.co.za

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you are not the intended recipient, any review, re transmission,
disclosure, copying, modification or other use of this email message or
attachments is strictly forbidden. If you receive this email message in
error, please contact the author and delete the message and any
associated files from your computer. Also the contents of this e-mail
is considered confidential.
Thank you



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





SPONSORED LINKS
Technical
<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&
w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq
+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technic
al+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> support Computer
<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical
+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&
w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsof
t+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA> technical support
Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g> computer
technical support
Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+s
upport&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4
=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+
technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w> technical support
Hewlett
<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA> packard
technical support Microsoft
<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technica
l+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support
&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microso
ft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw> technical
support

_____

YAHOO! GROUPS LINKS



* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .


_____




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