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.

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