More than 2 or 3 identical records : Delete Mysql

Question

I have a mysql table where I have more than 2 or 3 identical records. In a table, Joining on column1,column2,column3 and column4 have identical values then those records are considered duplicate. I dont care about the rest of the columns in the table but if the combination of above 4 columns are identical i want to delete all records for min(Id) keeping one single record of max (id)

  id,      site,      sector,       para1,       para2
'652',    'LCUE1015', '1',          '2275',       '0'
'166994', 'LCUE1015', '1',          '2275',       '0'
'5343',   'LCUE1015', '1',          '2275',       '0'
'166003', 'LCUE1015', '1',          '2275',       '0'
'76351',  'LCUE1015', '1',          '2275',       '0'
'77342',  'LCUE1015', '1',          '2275',       '0'

So here combination of `site`,`sector`,`para1` and `para2` defines a duplicate ? — Abhik Chakraborty, Mar 31 '16 at 07:44
yes, Correct !! I dont care about the rest of the columns in the table but if the combination of above 4 columns are identical i want to delete all records for min(Id) keeping one single record of max (id) — Priyanshu, Mar 31 '16 at 07:54
Try these solution http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql and let us know if you have any issue. This is fairly simple. — Abhik Chakraborty, Mar 31 '16 at 07:57
BTW, your track record on accepting/upvoting answers is atrocious. — Strawberry, Mar 31 '16 at 07:58

score 1 · Accepted Answer · answered Mar 31 '16 at 08:08

E.g. (more query than strictly necessary for this particular problem)

DELETE x 
  FROM my_table x 
  LEFT 
  JOIN 
     ( SELECT site
            , sector
            , para1
            , para2
            , MAX(id) id 
         FROM my_table 
        GROUP 
           BY site
            , sector 
            , para1
            , para2
     ) y 
    ON y.site = x.site
   AND y.sector = x.sector
   AND y.para1 = x.para1
   AND y.para2 = x.para2
   AND y.id = x.id  
 WHERE y.id IS NULL;

More than 2 or 3 identical records : Delete Mysql

1 Answers1