How to Delete Duplicate Rows in MySQL?

Duplicate rows are a common problem in MySQL databases. Duplicate rows can cause problems with data accuracy and integrity. They can also make it difficult to query and analyze data. They can occur for a variety of reasons, such as:

• Data entry errors
• Data import/export errors
• Database synchronization errors
• Software bugs

4 Ways to Delete Duplicate Rows in MySQL

There are a few different ways to delete duplicate rows from tables in MySQL:

1. Using the DELETE Statement
2. Using the DISTINCT Keyword
3. Using the GROUP BY Clause
4. Using the HAVING Clause

Demo MySQL Database

To try the methods mentioned above of deleting duplicate rows in MySQL, we will first create a table and insert duplicate values in it.

This table will be used in examples

MySQL

CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(255),
    email VARCHAR(255)
);
INSERT INTO customers (customer_id, customer_name, email)
VALUES
    (1, 'John Doe', 'john.doe@example.com'),
    (2, 'Jane Doe', 'jane.doe@example.com'),
    (3, 'Muzamil Amin', 'Muzamilaminitoo@gmail.com'),
    (1, 'John Doe', 'john.doe@example.com'), 
    (4, 'Alice Johnson', 'alice.johnson@example.com'),
    (2, 'Jane Doe', 'jane.doe@example.com');

Output:

Remove Duplicate Rows from Table in MySQL Examples

Let’s look at different ways to delete duplicate rows from a table in MySQL with these practical examples.

Remove Duplicate Rows Using the DELETE Statement

The DELETE statement can be used to delete duplicate rows from a table. The following is an example of how to use the DELETE statement to delete duplicate rows from the customers table:

DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    GROUP BY customer_id
    HAVING COUNT(*) > 1
);

This query will delete all of the duplicate rows from the customers table, where customer_id is the PRIMARY KEY.

Remove Duplicate Rows Using the DISTINCT Keyword

The DISTINCT keyword can be used to prevent duplicate rows from being returned in a query result. The following is an example of how to use the DISTINCT keyword to prevent duplicate rows from being returned in a query:

SELECT DISTINCT customer_id
FROM customers;

This query will return a list of all of the unique customer IDs in the customers table as shown in Table 2.

Output:

Remove Duplicate Rows Using the GROUP BY Clause

The GROUP BY clause can be used to group rows in a table by one or more columns. The following is an example of how to use the GROUP BY clause to group rows in the customers table by customer ID:

SELECT customer_id
FROM customers
GROUP BY customer_id;

This query will return a list of all of the unique customer IDs in the customers table, along with the number of rows associated with each customer ID.

Remove Duplicate Rows Using the HAVING Clause

The HAVING clause can be used to filter the results of a GROUP BY query. The following is an example of how to use the HAVING clause to filter the results of a GROUP BY query to only include groups with more than one row:

SELECT customer_id
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

This query will return a list of all of the customer IDs in the customers table that are associated with more than one row.

Conclusion

Duplicate rows can be a problem in MySQL databases. There are a few different ways as we discussed above to delete duplicate rows from MySQL tables. The best method to use depends on the specific situations like the number of duplicate rows, the size of the table, the performance of the MySQL server and the desired results.

In general, the DELETE statement is the most efficient way to delete duplicate rows from a table. However, if there are a large number of duplicate rows, it may be more efficient to use the DISTINCT keyword or the GROUP BY and HAVING clauses