Tweet
I used the following SQL statement to identify the no of duplicate records in FILE2. SELECT count(*) FROM QTEMP/FILE2 F1 WHERE RRN(F1) > (SELECT MIN(RRN(F2)) FROM QTEMP/FILE2 F2 WHERE F2.FHUYMD = F1.FHUYMD AND F2.FHRTE# = F1.FHRTE# AND F2.FHORIG = F1.FHORIG AND F2.FHDEST = F1.FHDEST and F2.FHFTKT = F1.FHFTKT) It came out with a result of 6. When I used the same criteria to get rid of the duplicate ones, I lost all the records in the file. DELETE FROM QTEMP/FILE2 F1 WHERE RRN(F1) > (SELECT MIN(RRN(F2)) FROM QTEMP/FILE2 F2 WHERE F2.FHUYMD = F1.FHUYMD AND F2.FHRTE# = F1.FHRTE# AND F2.FHORIG = F1.FHORIG AND F2.FHDEST = F1.FHDEST and F2.FHFTKT = F1.FHFTKT) 12 rows deleted from FILE2 in QTEMP. Can someone explain? Thank you, Srini
-
-
Deleting duplicate records
srini87 wrote: > I used the following SQL statement to identify > the no of duplicate records in FILE2. > > SELECT count(*) FROM QTEMP/FILE2 F1 WHERE RRN(F1) > (SELECT > MIN(RRN(F2)) FROM QTEMP/FILE2 F2 WHERE F2.FHUYMD = F1.FHUYMD AND > F2.FHRTE# = F1.FHRTE# AND F2.FHORIG = F1.FHORIG AND F2.FHDEST = > F1.FHDEST and F2.FHFTKT = F1.FHFTKT) I prefer this for duplicate record selection: Select fhuymd, fhrte#, fhorig, fhdest, fhftkt, count(*) From file2 group by fhuymd, fhrte#, fhorig, fhdest, fhftkt Having count(*) > 1 > DELETE FROM QTEMP/FILE2 F1 WHERE RRN(F1) > (SELECT > MIN(RRN(F2)) FROM QTEMP/FILE2 F2 WHERE F2.FHUYMD = F1.FHUYMD AND > F2.FHRTE# = F1.FHRTE# AND F2.FHORIG = F1.FHORIG AND F2.FHDEST = > F1.FHDEST and F2.FHFTKT = F1.FHFTKT) I don't know if I'd ever want to use SQL for dupe record check. When I find it necessary to do something like this, I use RPG for it, it's just too easy. Bill
Comment