MySQL Delete older records

Question

I have thousands of records (included duplicate posts) so now I want to delete old records (just leave the latest record) based on date.

My code is given below

DELETE a.*
FROM dle_post AS a
   INNER JOIN (
      SELECT title, MIN( id ) AS min_id
      FROM dle_post
      GROUP BY title
      HAVING COUNT( * ) > 1
   ) AS b ON b.title = a.title
AND b.min_id <> a.id

The problem is that it random records base on ID. I really appreciate your help!

I assume latest version of a post will have the biggest ID, so you should use Max insteas of Min to chose which ID will be saved — Stefano Zanini, Apr 08 '17 at 08:49

score 1 · Accepted Answer · answered Apr 08 '17 at 09:02

1

If you want to base it on date, you should use MAX(date) in the subquery.

DELETE a.*
FROM dle_post AS a
   INNER JOIN (
      SELECT title, MAX(date) AS maxdate
      FROM dle_post
      GROUP BY title
      HAVING COUNT( * ) > 1
   ) AS b 
ON b.title = a.title
AND a.date < b.maxdate

answered Apr 08 '17 at 09:02

Barmar

741,623
53
500
612

Thank you so much. – The Hung Apr 08 '17 at 11:14

score -1 · Answer 2 · answered Apr 08 '17 at 08:56

-1

Just create the SELECT query of posts you want to delete and put and in a subselect:

DELETE FROM dle_post WHERE id IN (SELECT id FROM dle_post WHERE ... )

This is more readable and maintainable.

answered Apr 08 '17 at 08:56

Bart Friederichs

33,050
15
95
195

That will get an error. See http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Barmar Apr 08 '17 at 08:57
OK. I have been using PostgreSQL a lot and I think there I can do this there. I learned something today. – Bart Friederichs Apr 08 '17 at 08:59
Also, it should be `NOT IN`, since he wants to delete all but the latest record in each group. – Barmar Apr 08 '17 at 08:59
It's an annoying restriction that MySQL has. – Barmar Apr 08 '17 at 09:00
I still leave my answer, as the answer you referred to also gives some ideas on how to address the issue. – Bart Friederichs Apr 08 '17 at 09:01

MySQL Delete older records

2 Answers2