Help with duplicate records in SQL

SQL can make you(me) crazy !!!

I have a table with some duplicate records. I would like to delete all duplications, leaving one of each record. I use essenitally the following search to find the duplicates:

  SELECT part, name, vendor, status
  FROM bigtable
  GROUP BY part, name, vendor, status

  HAVING COUNT(*) > 1                  <=== this is the key

To delete the redundencies, it seems natural to do something like this:

  DELETE FROM bigtable
  WHERE ROWNUM = 1
  AND (part, name, vendor, status) IN
   (SELECT part, name, vendor, status
    FROM bigtable
    GROUP BY part, name, vendor, status
    HAVING COUNT(*) > 1
   )
;

This does not work. The only way I can think to solve this (other than a "UNIQUE" clause when creating the table) is to create a temporary table. Then copy all "DISTINCT" records to it, drop the old table, and rename the new one. I could also write a Pro*C program, but there must/should be a better way.

Any help will be greatly appreciated !!!!!

Thanks,
Shawn
sbh_at_ncube.com Received on Thu Jun 04 1992 - 23:27:19 CEST