MySQL - Want to incrementally DELETE anything older than x amount of rows

Question

I've a bloated table without a timestamp field. So the best way I've come up with to clean this table up is to keep the most recent 7,000,000 rows (works out at about 1 year worth of data).

What I've been trying is to use the MAX(primary_id) - 7000000 and delete anything older than this in increments of 500 (LIMIT).

DELETE
FROM tblBloat
WHERE (Select MAX(primaryID) - 7000000 from tblBloat) > primaryID
ORDER BY primaryID desc
LIMIT 500

This however returns an error "You can't specify target table 'tblBloat' for update in FROM clause.

Also tried this, however mySQL doesn't seem to like the JOIN on a DELETE statement:

DELETE 
FROM tblBloat
INNER JOIN (select MAX(primaryID) - 7000000 as 'max'
FROM tblBloat) ab
WHERE primaryID < ab.max
ORDER BY primaryID DESC
LIMIT 500

Any ideas?

Answer 1

Get the highest ID that needs to be deleted with:

set @max_delete_id = (
  select primaryID
  from tblBloat
  order by primaryID desc
  limit 1
  offset 7000000
);

Then delete all rows with equal or smaller IDs:

delete
from tblBloat
where primaryID <= @max_delete_id
limit 500;

Then repeat the second query until no rows are affected.

I would though use a higher LIMIT (maybe 10000) or skip the LIMIT.

A common workaround for your query would be to wrap your subquery into a derived table (subquery in FROM clause):

DELETE
FROM tblBloat
WHERE (SELECT * FROM (Select MAX(primaryID) - 7000000 from tblBloat)x) > primaryID
ORDER BY primaryID desc
LIMIT 500

But note that primaryID might have gaps for several reasons, and you would keep less than 7M rows.