How To Remove Duplicates

BACKGROUND

Even the best designed databases, can on occassions, and under certain circumstances end up with duplicate data in a table. This document describes various procedures that can be used to remove those duplicate rows.

IDENTIFYING THE ROWS

  • Run the following peices of code against the offending table to identfiy the duplicate data:
SELECT 
   <column_name>, COUNT( <column_name> ) AS NumOccurrences
FROM
   <table_name>
GROUP BY
   <column_name>
HAVING 
   ( COUNT( <column_name> ) > 1 )
/

SELECT
   ROWID
FROM
   <table_name>
WHERE
   <column_name> IN <Results from previous query>
/
© copyright 2001-2014 ABCdba.com | all rights reserved