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

Question

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 ?

Answer 1

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;

Answer 2

How about not using delete at all? Write a query to save the records you want. Then truncate the table and insert them back in:

create temporary table tokeep as
    select h.*
    from history h
    where `date_field` > now() - INTERVAL 10 DAY
    union
    select h.*
    from history h
    order by date_field desc
    limit 20;

truncate table history;

insert into history  -- the only situation where I don't list the columns
    select *
    from tokeep;