English

How To: Query for duplicate records in a feature class table

Summary

Instructions provided describe how to query for duplicate records within an SDE or personal geodatabase feature class table.

If duplicate records exist based on the [FIELD_NAME] specified, ALL copies of the duplicate record will be selected. To distinguish between the first/original record and duplicates, refer to Knowledge Base article "Identify duplicate field values" in the Related Information section below.

Warning:
The procedure outlined is not available for data stored in file geodatabase or shapefile formats.


Note:
If the procedure below is done on a versioned ArcSDE feature class, the features stored in the delta tables are not considered. Because this query looks only at the business table of a feature class, ensure that all edits have been compressed before running this query. See article 29160 in the Related Information section below for details about achieving a full compress and move edits from the delta tables to the business table.

Procedure

Follow the steps below.

  1. Add SDE or personal geodatabase feature class to ArcMap.
  2. Click Selection > Select by Attributes from the Main Menu.
  3. For SDE data, copy the following SQL statement into the section labeled 'SELECT *FROM [TABLE_NAME] WHERE:'.

    Code:
    [FIELD_NAME] In (SELECT [FIELD_NAME] FROM [TABLE_NAME] GROUP BY [FIELD_NAME] HAVING Count(*)>1 )

    Replace FIELD_NAME and TABLE_NAME as necessary.
    Note:
    The SQL statement selects both copies of duplicate records. See the ESRI Knowledge Base article, "Identify duplicate field values," in the Related Information section below for a script that selects only the duplicate records with the larger OID value.

  4. For data stored in the personal geodatabase, copy the following SQL statement into the section labeled 'SELECT *FROM [TABLE_NAME] WHERE:'
    Code:
    [FIELD_NAME] In (SELECT [FIELD_NAME] FROM [TABLE_NAME] GROUP BY [FIELD_NAME] HAVING Count(*)>1 )


    Replace FIELD_NAME and TABLE_NAME as necessary.

Related Information