How to find duplicates?(s25)

I have a table --product--  where there are duplicates based upon the
product_id which was a key but was dropped(inmcreating athe table) and now
I need to create a pk based upon it but can't until I remove the
duplicates.

Can someone advise me how to code the delete from this table so that I can
remove the duplicate product_ids?


Ans:

The duplicates can be identified by:
  SELECT product_id, COUNT(1) FROM product
  GROUP BY product_id HAVING COUNT(1) > 1 ;
This will identify the duplicated rows.  To remove them:

  DELETE FROM product
  WHERE (product_id, rowid) IN (SELECT product_id, rowid FROM product
                                MINUS
                                SELECT product_id, MIN(rowid) FROM product
                                GROUP BY product_id)

The above subselect is sort of brute force, it gathers a list of all
product_id's and discards, via the MINUS, non-duplicated product_id's and the
"first" of the duplicated product_id's.  The resulting set is the duplicated
product_id's.  Please note that the product_id in the WHERE clause is not
needed, the rowid uniquely id's each row; but the SQL is confusing without
it. If your table is large, this will tie up resource.  Large tables with a
lot of duplicates are better handled via PL*SQL.  This may not work on Oracle
versions prior to 7.3.

HTH
    James
 

Hosted by www.Geocities.ws

1