DDS and DDL is irrelavant. Either can have, or not have, a primary key
constraint. DDS is just more likely because the odds of running into
legacy software that has no primary key, or the old codger who seems to
remember some old situation back on a 360 or 1401 having issues with
primary keys goes up.
The terminology may be what is throwing Matt. What many applications
called primary key was just a key on a logical over what should have been
the primary key but they often didn't even specify UNIQUE.

CREATE TABLE QTEMP.DUMMY (MYCHAR CHAR (5 ))
INSERT INTO QTEMP.DUMMY (MYCHAR) VALUES('A') with nc
INSERT INTO QTEMP.DUMMY (MYCHAR) VALUES('A') with nc
INSERT INTO QTEMP.DUMMY (MYCHAR) VALUES('B') with nc
INSERT INTO QTEMP.DUMMY (MYCHAR) VALUES('C') with nc
INSERT INTO QTEMP.DUMMY (MYCHAR) VALUES('D') with nc
INSERT INTO QTEMP.DUMMY (MYCHAR) VALUES('D') with nc
INSERT INTO QTEMP.DUMMY (MYCHAR) VALUES('D') with nc

select mychar, count(*), min(rrn(d))
from qtemp.dummy d
group by mychar
having count(*)>1
order by mychar
MYCHAR COUNT ( * ) MIN ( RRN ( D ) )
A 2 1
D 3 5

http://centerfieldtechnology.com/PDFs/JulyAugust07.pdf
delete from qtemp.dummy d
where rrn(d) in
(select rrn(b)
from qtemp.dummy b,
(select a.mychar, min(rrn(a)) minrrn
from qtemp.dummy a
group by a.mychar) as c
where b.mychar = c.mychar and rrn(b) > c.minrrn)
with nc
3 rows deleted from DUMMY in QTEMP.

select mychar, rrn(d)
from qtemp.dummy d

MYCHAR RRN ( D )
A 1
B 3
C 4
D 5

BTW, I searched archive.midrange.com with your subject line, for this
answer.

Rob Berendt

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].