1

I have duplicate records I accidentally inserted into a table, and I have found them using a count() query, like so:

select _clm1, count(_id) as count from _tableName group by _clm1 having count > 1;

Is there another query I can append to this, to erase the duplicate entries, while leaving one of each untouched?

Ben
  • 2,433
  • 5
  • 39
  • 69
  • possible duplicate of [How do I delete all the duplicate records in a MySQL table without temp tables](http://stackoverflow.com/questions/14046355/how-do-i-delete-all-the-duplicate-records-in-a-mysql-table-without-temp-tables) – SpringLearner Mar 03 '15 at 05:16

2 Answers2

2
DELETE FROM _tableName WHERE _id NOT IN(SELECT _id FROM _tableName GROUP BY _clm1)
winston86
  • 159
  • 1
  • 8
  • Big mistake - you dropped the HAVING clause - if there are entries in his table which aren't duplicates, you'll delete the only copy of those with this query. – Joel Cox Mar 03 '15 at 05:00
  • This query should delete all duplicates of `_clm1` . – winston86 Mar 03 '15 at 05:10
  • Yes... But I thought it was worth pointing out that if there is any single records in the table which are _not_ duplicates, your query will also delete those. You should add the `having` clause back into the subquery to prevent that, as it's most likely not what OP has in mind. (this may or may not apply to him, but for future readers this is still important to point out I think). – Joel Cox Mar 03 '15 at 05:19
  • Well you can try it. Subquery should select all rows and group them by `_clm1` and should not select duplicates because of `GROUP BY` – winston86 Mar 03 '15 at 05:41
  • `GROUP BY` can return "_groups_" of a single record. See example at http://sqlfiddle.com/#!2/64c52/2 - I insert records with values (1,2,2) then select with `GROUP BY` and you do see the single record with value 1 in the result set. Adding having COUNT(*)>1 removes the single record. – Joel Cox Mar 03 '15 at 11:36
  • Read my query again. It is ` NOT IN()` not ` IN()` condition. – winston86 Mar 04 '15 at 04:45
  • You're absolutely right. I apologise for my misunderstanding. – Joel Cox Mar 04 '15 at 11:26
0

First find out the id's of the rows having duplicates from your above select query

Then fire Delete Query to remove only those repeated entries by their id.

rohitr
  • 371
  • 2
  • 11