Re: Deleting duplicate rows

Another Solution:

Suppose your Table T was supposed to have fields F1, F2, F3 as a composite unique key and you had (for some reason) or forgotten, to create a unique index.

Use the SQL below to identify the duplicated rows:

    SELECT rowid

      FROM T  T1
     WHERE rowid > (SELECT MIN(rowid)
                      FROM T T2
                     WHERE T1.F1 = T2.F1
                       AND T1.F2 = T2.F2
                       AND T1.F3 = T2.F3) ;

If you have:

(1) a small table, convert the above SELECT to a DELETE and

    delete in one shot
(2) a large table, wrap it in a PL/SQL by defining a cursor,

    fetching in a loop and delete
(3) a very large table, use PRO*C and delete in batches

Regards,

Rajiv Tandon Received on Wed Nov 29 1995 - 00:00:00 CET