Why would you have duplicate records? The primary key on the table should prevent duplicate record insertions. Unless you are still using DDS defined files, in which case you should fix your programs to prevent duplicate insertions like the database would normally do for you if it was a DDL described file with a primary key defined.

At the very least your duplicate removal should not use RRN's, instead it should use the key of the table to remove the duplicates.

-----Original Message-----
From: Alan Shore [mailto:ashore@xxxxxxxx]
Sent: Monday, October 5, 2015 10:07 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Using sql to delete duplicate records

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 is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

This thread ...


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