SQL to remove rows with duplicated value while keeping one
I want to add MYSQL solution for this query
Suggestion 1 : MySQL prior to version 8.0 doesn't support the WITH clause
Suggestion 2 : throw this error (you can't specify table TableName for update in FROM clause
So the solution will be
DELETE FROM TableName WHERE id NOT IN
(SELECT MIN(id) as id
FROM (select * from TableName) as t1
GROUP BY data,value) as t2;
You can use one of the methods below:
Using
WITH CTE
:WITH CTE AS (SELECT *,RN=ROW_NUMBER() OVER(PARTITION BY data,value ORDER BY id) FROM TableName) DELETE FROM CTE WHERE RN>1
Explanation:
This query will select the contents of the table along with a row number RN
. And then delete the records with RN
>1 (which would be the duplicates).
This Fiddle shows the records which are going to be deleted using this method.
Using
NOT IN
:DELETE FROM TableName WHERE id NOT IN (SELECT MIN(id) as id FROM TableName GROUP BY data,value)
Explanation:
With the given example, inner query will return ids (1,6,4,5,7). The outer query will delete records from table whose id NOT IN (1,6,4,5,7).
This fiddle shows the records which are going to be deleted using this method.
Suggestion: Use the first method since it is faster than the latter. Also, it manages to keep only one record if id
field is also duplicated for the same data
and value
.