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.
midrange.com
