Delete the maximum ID row

Question

How can I find duplicate rows and for each group of duplicate rows delete the max ID row, in MySQL?

Using this query, I can find duplicate rows:

SELECT * , COUNT( awb_no ) c
FROM tbl_cod_rpt_COPY
GROUP BY awb_no
HAVING c >1

And this query gives me the maximum ID for each group:

SELECT * , MAX( tbl_cod_rpt_id )
FROM tbl_cod_rpt_COPY
GROUP BY awb_no
HAVING COUNT( awb_no ) >1

Now how would I delete the tbl_cod_rpt_COPY rows matching the MAX( tbl_cod_rpt_id ) values from the query above?

score 2 · Accepted Answer · answered May 30 '16 at 11:58

MySQL allows a join in a DELETE statement. You can use this feature to first get a derived table, similar to the query you have that finds all maximum ids for groups that have more than 1 row, then join this derived table to the table itself.

The following assumes that (tbl_cod_rpt_id) is the primary key of the table:

DELETE 
    t.*
FROM 
    ( SELECT awb_no, MAX(tbl_cod_rpt_id) AS tbl_cod_rpt_id
      FROM tbl_cod_rpt_COPY
      GROUP BY awb_no
      HAVING COUNT(*) > 1
    ) AS dup
JOIN 
    tbl_cod_rpt_COPY AS t
  ON 
    t.tbl_cod_rpt_id = dup.tbl_cod_rpt_id ;

Delete the maximum ID row

1 Answers1