Thanks Rob
That seems to have done the trick

This is where I originally found my sql script
http://iprodeveloper.com/database/deleting-records-duplicate-data-revisited



Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, October 05, 2015 1:08 PM
To: Midrange Systems Technical Discussion
Subject: 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
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Alan Shore <ashore@xxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 10/05/2015 11:07 AM
Subject: Using sql to delete duplicate records
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Hi everyone
Before I forget - we are on V5r4

This is driving me nuts (I know - not that far to drive)
I used the following sql to delete duplicate records in a file
delete from file01 A where rrn(A) in
(select RRN(B) from file01 B, file01 C where
b.IITEM = c.IITEM and RRN(B) > RRN(C))


and received a display the nnn records had been deleted
Don't ask me why - but I ran the same statement again - expecting zero
records deleted
But it gave me a smaller number than before
Which surprised me
So I ran it again
And received an even smaller number

I ran this statement multiple time, receiving a smaller number each time

I then ran a RGZPFM to remove all deleted records and ran the sql
statement again
Only to be informed that a certain number had been deleted
I ran it again and received notification tha a lesser number had been
deleted


Obviously I am doing something wrong - but I cannot see what it is
Does anyone else see what I am doing wrong?

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill


This thread ...

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