Handling unintended duplicate rows/records from MySQL (or any database).

19th October 2009 16:32:48 by Richard Bennett

Querying for duplicates

select columnWithDuplicateValue,count(*) as n from tableName group by columnWithDuplicateValue having n >1;

Querying for duplicates with additional criteria

select columnWithDuplicateValue,count(*) as n from tableName where additionalColumns = 'something' group by columnWithDuplicateValue having n >1;

Deleting duplicates

Copy the result table from the above, and create the following statement for each line.

delete from tableName where columnWithDuplicateValue = 'the value from the result table' limit 1;

Deleting duplicates with additional criteria

Copy the result table from the above, and create the following statement for each line.

delete from tableName where columnWithDuplicateValue = 'the value from the result table' and additionalColumns = 'something' limit 1;

Pregging query results

For MySQL result tables, copy/paste the result table into an editor and perform the following regular expression find/replaces, to create a list of delete statements.

Find = "\| (.*?) | \d* |" (without the double quotes)

Replace = "delete from tableName where columnWithDuplicateValue = '\1' limit 1;" (without the double quotes)

Querying duplicates with multiple distinguishing columns

select columnWithDuplicateValue,2ndColumnWithDuplicateValue,count(*) as n from tableName group by columnWithDuplicateValue,2ndColumnWithDuplicateValue having n > 1;

Deleting duplicates with multiple distinguishing columns

Copy the result table from the above, and create the following statement for each line.

delete from tableName where columnWithDuplicateValue = 'the value from the result table' and 2ndColumnWithDuplicateValue = 'the other value from the result table' limit 1;