如何在MySQL中查找重复的字段值并将其删除

Question

Consider we have a field named (username) in our table named (tpl_users), now this table has lots of duplicated rows

I wrote this code to delete duplicated usernames:

Delete FROM tpl_users WHERE username = username;

How is it possible to delete duplicated usernames?

Answer 1

Your query deletes all the rows where the user name is not NULL.

If you want to identify the user names which are associated with more than one row:

SELECT username
  FROM tpl_users
 GROUP BY username
HAVING COUNT(*) > 1;

Before converting that into a DELETE, you need to be aware that the majority of queries will delete all the records associated with a duplicated name, rather than retaining just one of the duplicates.