Finding Duplicate Values In A MySQL Table

Published by philipnorton42 on Wed, 02/06/2008 - 16:31

To find duplicate values you need to use the MySQL COUNT() function and then pick out all of the counts that are greater than one.

1 2	1) ORDER BY Count DESC;

Conversely you can also select the rows that only have a single entry.

<p>However, it is very nice to pick out the duplicate entries in a table, but you might still need to do something with them.  Here is a query to delete any duplicate rows from a table.  It does a simple self join and deletes the row value with the lowest ID.</p>
 
<pre language="sql}DELETE bad_rows.*
FROM tests AS good_rows
INNER JOIN tests AS bad_rows ON bad_rows.number = good_rows.number
AND bad_rows.id > good_rows.id;

More information on this deletion query and other methods of deleting duplicates can be found at Xaprb.com.

Category:

Tags: