This query returns the count for duplicate columns col B, col C, col D
SELECT `colB`, `colC`, `colD`, COUNT(1) as CNT
FROM `table`
GROUP BY `colB`, `colC`, `colD`
HAVING COUNT(1) > 1
How do I delete the duplicate records while keeping one ?
DELETE FROM `table` WHERE (
// Keep one record and delete rows with duplicate columns b,c,d
)
colA is the Primary Key - AI
If you have a primary key, you can do:
delete t
from table t left join
(select cola, colb, colc, min(cola) as cola
from table t
group by cola, colb, colc
) tokeep
on t.cola = tokeep.cola
where tokeep.cola is null;
However, lots of deletes on a table can be inefficient. So, it is often faster to move the data to a temporary table an re-insert it:
create temporary table tokeep as
select min(cola) as cola, colb, colc, cold
from table t
group by colb, colc, cold
truncate table `table`;
insert into `table`(cola, colb, colc, cold)
select cola, colb, colc, cold
from tokeep;
try this
DELETE a
FROM table a
LEFT JOIN
(
SELECT MIN(colA) colA , colB , colC ,colD
FROM Table
GROUP BY colB , colC,colD
) b ON a.colA = b.colA and a.colB = b.colB and a.colC = b.colC and a.colD = b.colD
WHERE b.colA IS NULL
Try something like:
DELETE t1
FROM table t1 INNER JOIN table t2
ON t1.colB = t2.ColB
AND t1.colC = t2.ColC
AND t1.colD = t2.ColD
AND t1.colA > t2.ColA
