Re: removing duplcates --sql question

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

In article <01be90e1$34729b40$12f21cac_at_dspaisman>,   "David Spaisman" <david.spaisman_at_compaq.com> wrote:
> Hello:
>
> I have a basic sql question.
> 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?
>
> Thanks.
>
> David Spaisman
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Apr 27 1999 - 15:34:38 CDT