MySQL Find Duplicate Records (Rows) | MySQL Tutorial

MySQL Find Duplicate Records (Rows) | MySQL Tutorial

MySQL finds duplicate records(rows). This tutorial shows you how you can easily find duplicate records or rows in the database table. Also, you will learn how to find and delete duplicate rows keep ones in a database table.

The find duplicate rows tutorial explains to you 3 easy query to find duplicate records or rows in your database table. And also delete duplicate rows keep one.

If you are finding MySQL query for find duplicate rows or record and delete duplicate records or data from the MySQL database table. This tutorial will help you to find and delete duplicate rows but keep one without a primary key, delete duplicate records in MySQL except one, delete all duplicate rows except for one in MySQL, MySQL delete duplicate rows keep latest.

MySQL Find Duplicate Rows and Delete

  • The first way – Find duplicate rows
  • The second way – Find duplicate records
  • MySQL delete duplicate rows but keep one

The first way Find duplicate rows

Here, you will learn how you can find the duplicate records with the count in your database table.

Let’s take an example, let’s you have one table name users and where we will check duplicate rows using the email column in the users table. So you can use the below MySQL query to find duplicate rows in your database table with the count.

SELECT
    id,
    COUNT(email)
FROM
    users
GROUP BY email
HAVING COUNT(email) > 1;

Output – Find duplicate rows

The above query output looks like this:

 +---------------------+---------------------+
 | id                  | Count               |
 +---------------------+---------------------+
 | 2                   | 5                   |
 +---------------------+---------------------+
 | 4                   | 3                   |
 +---------------------+---------------------+

The second way – Find duplicate records

Here, you will learn second easy way to find the duplicate records in your database table.

Let’s take the second example, let’s you have one table name users and where we will check duplicate rows using the email column in the users table. So you can use the below MySQL query to find duplicate rows in your database table with the count.

SELECT id, email
FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING count(email) > 1
    )
ORDER BY email

Output – Find duplicate records

The above query output looks like this:

 +---------------------+---------------------+
 | id                  | Email               |
 +---------------------+---------------------+
 | 2                   | [email protected]      |
 +---------------------+---------------------+
 | 3                   | [email protected]      |
 +---------------------+---------------------+
 | 4                   | [email protected]       |
 +---------------------+---------------------+ 
 | 5                   | [email protected]       |
 +---------------------+---------------------+

MySQL delete duplicate rows but keep one

Let’s take an example, Here you will learn how you can keep one row and delete remaining rows in MySQL database table. So use the below query for MySQL delete duplicate rows but keep one:

delete users
   from users
    inner join (
     select max(id) as lastId, email
       from users
      group by email
     having count(*) > 1) duplic on duplic.email = users.email
  where users.id < duplic.lastId;

The above query output is look like this:

how to delete duplicate records in mysql keeping one record

Conclusion

In this MySQL tutorial, you have learned how you can find duplicate rows or records in your database table. And also you have learned how you can keep one row and delete remaining rows in MySQL database table.

You may like

  1. Get month name from date in mysql
  2. mysql get first day of the current month
  3. mysql last day of previous month
  4. Get month number from month name in mysql
  5. mysql get day of week number from date
  6. Mysql Get Data Of Current Date, Week, Month, YEAR
  7. Get year of week in MySQL
  8. Query For Get Data Of Last Day, Week, Month, YEAR – Mysql
  9. TIMESTAMP() Function Examples – MySQL
  10. SEC_TO_TIME() Function Examples – MySQL
  11. QUARTER() Function Examples – MySQL
  12. STR_TO_DATE() Function Examples – MySQL
  13. PERIOD_DIFF() Function Examples – MySQL
  14. PERIOD_ADD() Function Examples – MySQL
  15. SECOND() Function Examples – MySQL
  16. MICROSECOND() Function Examples – MySQL
  17. MAKETIME() Function Examples – MySQL
  18. Localtime() Function Examples – MySQL
  19. CURTIME() Function Examples – MySQL
  20. MySQL CURRENT TIMESTAMP Function Examples
  21. MySQL CURRENT TIME Function Examples
  22. ADDTIME() Function Examples – MySQL
  23. MINUTE() Function Examples – MySQL
  24. HOUR() Function Examples – MySQL
  25. NOW() MySQL Function | MySQL
  26. MySQL DATE() And TIME() Functions (List)
  27. TIMESTAMPADD() Function Examples – MySQL
  28. TIMESTAMPDIFF() Function Examples – MySQL

If you have any questions or thoughts to share, use the comment form below to reach us.

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Javascript, JQuery, Laravel, Livewire, Codeigniter, Vue JS, Angular JS, React Js, WordPress, and Bootstrap from a starting stage. As well as demo example.

One reply to MySQL Find Duplicate Records (Rows) | MySQL Tutorial

  1. I was finding this mysql query of “mysql delete duplicate rows but keep one”. This is very useful for me.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *