The solution relies on the following two principles:
- Identical rows with different rowid's
- joining the table by itself
delete from T1 A WHERE a.rowid > ANY ( SELECT B.rowid FROM T1 B WHERE A.c1 = B.c1 and A.c2 = B.c2 and A.c3 = B.c3 )
So how does the script work? The columns c1-c3 identify the composite keys of the row which will make it a unique entry in the table. Thus the subquery will join the table with itself and returns identical rows with a different rowid from the parent query. The delete statement will remove the row with the larger rowid, i.e. the duplicate.