How to delete duplicate rows in a table?
We are on V6R1.
After reorganizing a physical file (table STXH) with command "RGZPFM FILE(R3AP5DATA/STXH)"
I found out that some duplicate records exist.
Then I used Note 116192 - IBM i: SQL error -7008 to carry on (reason code 9, case a).
But the command DSPPFM R3<SID>DATA/<file name> in step 5 does not show any useful data
so I can't find out which records are duplicate ones.
Another try was this SQL command using the "record row number" but it runs forever (I had to cancel it after 22 hours runtime):
DELETE FROM STXH F1 WHERE RRN(F1) < (select MAX(RRN(F2)) FROM STXH F2 WHERE F2.MANDT=F1.MANDT AND F2.TDREFOBJ=F1.TDREFOBJ AND F2.TDREFNAME=F1.TDREFNAME AND F2.TDREFID=F1.TDREFID)
Any idea how to get rid of these duplicate rows?
P.S. The table STXH is quite big: approx. 48 millions rows.
Rüdiger Höckel replied
The solution was the following SELECT statement which was much quicker to find out the duplicate rows:
table STXH has these key fields (all CHAR):
select all * from STXH T01 where 1 < (select count(*) from STXH T02 where T01.MANDT = T02.MANDT AND T01.TDOBJECT = T02.TDOBJECT A
ND T01.TDNAME = T02.TDNAME AND T01.TDID = T02.TDID AND T01.TDSPRAS = T02.TDSPRAS) ORDER BY T01.MANDT, T01.TDOBJECT, T01.TDNAME, T
|01.TDID, T01.TDSPRAS asc|