CODEWITHSUNDEEP
mysqlduplicates
Scoop
Scoop

Reputation: 67

Deleting duplicate rows, keeping most recent row by max(datetime)

The MySQL statement below selects all rows that have duplicates based on ADDRESS1 and POSTCODE of which the most recent row, INSPECTION_DATE. Essentially it selects all the ones I want to keep.

SELECT COUNT(*), MAX(INSPECTION_DATE), ADDRESS1, POSTCODE, id
FROM epc 
GROUP BY ADDRESS1, POSTCODE 
HAVING COUNT(*) > 1;

My question is, how do I delete all of the older duplicates, keeping the ones that the above statement returns.

Upvotes: 1

Views: 68

Answers (1)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/8de866/1

DELETE  e
FROM epc e
INNER JOIN epc max_
ON e.address1 = max_.address1
   AND e.postcode = max_.postcode
   AND e.inspection_date < max_.inspection_date;

UPDATE http://sqlfiddle.com/#!9/5bd5981/1

DELETE  e
FROM epc e
INNER JOIN epc max_
ON e.address1 = max_.address1
   AND e.postcode = max_.postcode
   AND (e.inspection_date < max_.inspection_date
        OR (e.inspection_date = max_.inspection_date
           AND e.id < max_.id));

Upvotes: 1

Related Questions