how to delete Duplicate records

[g_rajesh_aash]

Hi


We have some duplicate records in file. how can i delete the duplicate ones with reataining original . ( If i have 3 records with duplicate on acct# fileld one shud be there retained and other two should be deleted )


Plz. help me in this issue.

[kpmac]

Re: how to delete Duplicate records

--Make sure you save the file with data before performing the following satements
-- Dup = table containg duplicates
-- NonDup = table wo Duplicates

Create Table NonDup Like Dup
Insert Into NonDup (Select Distinct * from Dup)

--After you verify the results are correct
Delete from Dup
Insert Into Dup (Select * From NonDup)

[jamief]

Re: how to delete Duplicate records

example

Delete From CODE400/CUSTOMERP A                        
    Where RRN(A) >                                     
          (Select Min(RRN(B)) From CODE400/CUSTOMERP B 
             Where A.LastName = B.LastName)

[Tho_Ros]

This was exactly what I needed!
Thanks a ton Jamie.

[pjk]

Re: how to delete Duplicate records

Assuming you use an SQLRPGLE program you could build a cursor to read the file in in acct # order. Then check the acct # against a temp field where you store the last read acct #, and if the current FETCH acct # is equal to this field (current acct # equal last acct #), then DELETE (execute SQL to delete)--keep doing that for each subsequent FETCH that yields the same condition.