MySQL Specialists: Delete all rows older than x days but not the latest one.

Question

first of all, this is the query which creates the "player history" it can be executed as often as you want and it will only create new history rows for the players if there is no history row for yesterday or if the values changed since the latest history entry in the past.

INSERT INTO `player_history` (`player_id`, `date`, `races`, `maps`, `playtime`, `points`)
SELECT `p`.`id`, DATE_SUB(NOW(), INTERVAL 1 DAY), `p`.`races`, `p`.`maps`, `p`.`playtime`, `p`.`points`
FROM `player` `p`
WHERE `p`.`playtime` IS NOT NULL
AND `p`.`playtime` > 0
AND (
    SELECT `player_id`
    FROM `player_history`^
    WHERE `player_id` = `p`.`id`
    AND (
        `date` = DATE_SUB(NOW(), INTERVAL 1 DAY)
        OR (
            `date` < DATE_SUB(NOW(), INTERVAL 1 DAY)
            AND `races` = `p`.`races`
            AND `points` = `p`.`points`
            AND `maps` = `p`.`maps`
            AND `playtime` = `p`.`playtime`
        )
    )
    ORDER BY `date` DESC
    LIMIT 1
) IS NULL;

now the problem is i also want to cleanup the history table using a single query. this already selects all history entries older than 10 days but the latest. but i cant just like do DELETE instead of SELECT *.

SELECT *
FROM `player_history` `ph`
WHERE `date` < DATE_SUB(NOW(), INTERVAL 10 DAY)
AND `date` != (SELECT `date`
    FROM `player_history`
    WHERE `player_id` = `ph`.`player_id`
    ORDER BY `date` DESC
    LIMIT 1);

so is tehre a way to do what i want using a single delete query?

Answer 1

Your query looks right in my eyes but you don't have the interval in the subquery.

I would do this:

DELETE FROM player_history
WHERE date < DATE_SUB(NOW(), INTERVAL 10 DAY)
AND date != (
    SELECT MAX(date) FROM player_history
    WHERE date < DATE_SUB(NOW(), INTERVAL 10 DAY)
)

What's the error message from mysql?

Answer 2

Probably you can't do this in a single query because the documentation states:

Currently, you cannot delete from a table and select from the same table in a subquery.

As a workaround you could select the ids of the rows that have to be deleted into a temporary table and then use a multi-table delete statement to delete the records from the original table.