0

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

Evan Carslake
  • 2,267
  • 15
  • 38
  • 56
Awena
  • 1,002
  • 5
  • 20
  • 43

3 Answers3

3

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

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
ashkufaraz
  • 5,179
  • 6
  • 51
  • 82
0

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
SMA
  • 36,381
  • 8
  • 49
  • 73