This example will show you how to delete or eliminate duplicate records from a table in database. Keeping duplicate records in a table is unnecessary and it also grows the size of database unnecessary. Removing duplicate records from the table will free the occupied space and enhance also the performance of the query execution.

Let’s say you have the following data in your database table. The table name is teacher. The following table has some duplicate records. Now I will show you how to remove duplicates from the below table.

In MySQL database you can use any of the following query. If you want to keep the rows with minimum id values then you can use the following query.

DELETE FROM teacher
 WHERE id NOT IN (SELECT * 
                    FROM (SELECT min(t.id)
                            FROM teacher t
                        GROUP BY t.name) x);

Or you can execute the following query:

DELETE t1 FROM teacher t1, teacher t2 WHERE t1.id > t2.id AND t1.name = t2.name;

Or you can use the following query:

DELETE t1 FROM teacher t1
INNER JOIN teacher t2
WHERE
    t1.id > t2.id
    AND t1.name = t2.name;

You will see the following output once you execute the above query.

In the above query the x is an alias which is required otherwise you will see the error Every derived table must have its own alias.

If you want to keep the rows with maximum id values then you can use the following query.

DELETE FROM teacher
 WHERE id NOT IN (SELECT * 
                    FROM (SELECT max(t.id)
                            FROM teacher t
                        GROUP BY t.name) x);

Or you can execute the following query:

DELETE t1 FROM teacher t1, teacher t2 WHERE t1.id < t2.id AND t1.name = t2.name;

Or you can use the following query:

DELETE t1 FROM teacher t1
INNER JOIN teacher t2
WHERE
    t1.id < t2.id
    AND t1.name = t2.name;

You will see the following output after removing duplicate records.

The above queries can be used in MySQL version 5.1 to 8.x.

You can also use ROW_NUMBER() from MySQL 8 to delete duplicate rows from the table.

DELETE FROM teacher 
WHERE 
	id IN (
	SELECT 
		id 
	FROM (
		SELECT 
			id,
			ROW_NUMBER() OVER (
				PARTITION BY name
				ORDER BY name) AS row_num
		FROM 
			teacher
		
	) t
    WHERE row_num > 1
);

The above query will give you the following output.

That’s all about how to delete or remove duplicate rows from MySQL table.

Source Code

Download