Re: Removing duplicate records

John,

There are several ways to identify (and optionally delete) duplicate
records with SQL.

An approach that (albeit a little bit slow) allows you to easily check your
data could be as follows:


*CREATE VIEW MYLIB/MYVIEW AS *

* **WITH** T1 AS( *

*SELECT A.*,** RRN(A) AS RRNO *

*FROM MYLIB/MYFILE A *

*) *

*SELECT * FROM T1 A WHERE EXISTS( *

* SELECT 1 FROM T1 B *

* * WHERE A.ACCOUNT = B.account AND A.RRNO > B.RRNO

*)*

*;*

If you do a SELECT * FROM MYVIEW you would see only your duplicate records.
To delete them, use DELETE FROM MYVIEW (please backup beforehand!!).

Of course, an index over the accout column would help a lot.

Also, please remember that if you had defined your SQL with either a
PRIMARY KEY or a UNIQUE constraint referencing the Acct. number, this would
have been, as you say, an issue.

HTH,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries