Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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?

Cheers,

   Rüdiger

P.S. The table STXH is quite big: approx. 48 millions rows.

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):

MANDT   3  

TDOBJECT    10

TDNAME   70
TDID   4
TDSPRAS   1

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          
0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question