mysqlsqlmysql-5.0

Delete records that older some days, but if it delete all, keep last 20 records

i have something like this:

DELETE FROM `history` WHERE `date_field` <= now() - INTERVAL 10 DAY

but if all records is older than 10 days - this query deletes all! i want to keep last 20 records, even if they too old !

Please help, what and how i need to upd my code, and what will be better use limit+offset of windowed function OVER() or need smth another ?

Solution

  • Join with a subquery that gets the most recent 20 days and excludes them.

    DELETE h1 
FROM history AS h1
LEFT JOIN (
    SELECT id
    FROM history
    ORDER BY date_field DESC
    LIMIT 20
) AS h2 ON h1.id = h2.id
WHERE date_field < now() - INTERVAL 10 DAY
AND h2.id IS NULL;