Finding Duplicate Values In A MySQL Table

Wednesday, February 6, 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.

1
2
3
4
5
6
<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: 
philipnorton42's picture

Philip Norton

Phil is the founder and administrator of #! code and is an IT professional working in the North West of the UK.
Google+ | Twitter

Add new comment