Re: Using sql to delete duplicate records

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