<p>How to remove duplicate MySQL records (but keep only one)</p> <p>Hi everyone, I have a problem, I have several records with the same ID and I want to remove duplicate records but keep only one. Any ideas on using mysql statements? </p> <p>I have this statement to see the number of records and duplicates, but it doesn't work for me when I use the delete statement: </p> <pre class="brush:php;toolbar:false;">SELECT email, COUNT(email) FROM contacts GROUP BY email HAVING COUNT(email) > 1;</pre> <p>I use this statement, but it only removes one duplicate record: </p> <pre class="brush:php;toolbar:false;">DELETE FROM wp_options WHERE option_id=5 limit 1;</pre> <p>Is there a way to batch process? </p> <p>Update: I'm using this statement, but it removes all duplicate records without keeping one: </p> <pre class="brush:php;toolbar:false;">DELETE FROM xhi_options WHERE option_id IN (SELECT option_id FROM (SELECT option_id FROM xhi_options GROUP BY option_id HAVING COUNT(option_id) > 1) AS T)</pre></p>

DELETE FROM wp_options WHERE option_id=5 limit (dupl_rec_count - 1);

DELETE e1 FROM contacts e1, contacts e2 WHERE e1.id > e2.id AND e1.email = e2.email;

DELETE e1 FROM contacts e1, contacts e2 WHERE e1.id < e2.id AND e1.email = e2.email;