Re: Eliminating Dup Records

M Rajeshwar <MR1154_at_CAMBER.COM> wrote:

>Since the primary key constraints were disabled, one of my tables now is
>flooded with duplicate records. I had known a method of maintaing
>unique records, deleting duplicate records, but not able to recall it.
 

>Can anyone help me out in this regard ?
 

>Thanks
 

>Raj

Say Table1 has F1, F2 etc where F1, F2 consitute the PK.

DELETE from Table1 T1
WHERE (T1.F1, T1.F2) in (SELECT T2.F1, T2.F2

                                           FROM    Table1 T2
                                          GROUP BY T2.F1, T2.F2
                                          HAVING COUNT(*) > 1)
AND  T1.ROWID > (SELECT MIN(T3.ROWID)
                                FROM    Table1 T3
                                WHERE T3.F1 = T1.F1
                                AND       T3.F2 = T1.F2)

I hope this helps. Received on Fri Jul 19 1996 - 00:00:00 CEST