Monday, June 03, 2002

How to Delete Duplicate Records Using Oracle's Rank Function

I've found a neat way to delete duplicates from my database tables using Oracle's RANK() function.
DELETE FROM __table__
WHERE ROWID IN (
  SELECT MyKey
  FROM (
    -- use the RANK() function to assign a sequential number to each set of
records.
    SELECT MyKey, display_name, RANK() OVER (PARTITION BY display_name ORDER BY MyKey) AS SeqNumber
    FROM (
      -- use the ROWID psuedo-column to get a unique id for each record.
      SELECT ROWID AS MyKey, display_name
      FROM __table__
      WHERE (display_name) IN (
        -- select the set of records that are duplicates.
        SELECT display_name FROM __table__ GROUP BY display_name HAVING COUNT(*) > 1
      )
    )
  )
  WHERE SeqNumber > 1
)
Post a Comment