Problem solve Get help with specific problems with your technologies, process and projects.

Delete duplicate records using SQL in iSeries 400

Get rid of duplicate records in a file by using a simple SQL statement.

This tip is useful in getting rid of duplicate records in a file. Instead of writing a RPG pgm, users can just accomplish it using a simple SQL statement. However, I advice people to be judicious in using it based on file size. The SQL performance may not be as good as the native file access.

Delete from File1 F1 where RRN(f1) > (select MIN(RRN(F2)) from File1 F2 where F2.Field1 = F1.Field1)

If there is more than one field as key add them as part of the WHERE clause. Note that F1 and F2 are pointing to the same file.


The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.

Dig Deeper on iSeries CL programming