It’s happened most of time, as i’m not only who face this problem “a duplicate rows”.
So what’s you do when you find that your database has lots of duplicate eateries.
How you filter them ?
How you delete them ?
No idea, let’s try this to today.
First i’ll try to find duplicate rows.
Let’s suppose there is ‘users’ table in database which has lot’s of duplicate values.
+----+--------+------+--------
| id | name | email |
+----+--------+------+--------
| 1 | san | san@gmail.com |
| 2 | joe | joe@gmail.com |
| 3 | san | san@gmail.com |
| 4 | san | deago@gmail.com |
| 5 | joe | lobo@gmail.com |
+----+-------------------------Definitely you’ll find lots of solution for finding duplicate rows in mysql table,but i’m showing you one of the best query –
Let’s do it –
SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) > 1Output :
+----+--------+------+--------
| name | email | count|
+----+--------+------+--------
| san | san@gmail.com | 2 |
+----+-------------------------Now let’s delete duplicate rows.
DELETE FROM users A WHERE ROWID > (SELECT min(rowid) FROM users B WHERE A.name= B.name);
