Delete from Mysql table where datetime is older than datetime of another item

Question

I have some difficulty to get my MySQL query right. The current status is like this:

I have a table containing Todo's with a datetime field changed_at (set to current datetime on insert/update)

I want to build a query that checks on insert/update:

if there are more than 20 items with is_checked value set to 1
if yes, delete all items that are older than those 20 items

Additional information:

Table structure:
example data:

Expected result from such a query would be, that all todo's with datetime of field created_at older than the newest 20 (or x) items are deleted.

I'm a bit stuck here, because this seems to be a more complex query. I already read through the MySQL docs about date and time functions but I don't get how to achieve this. Can anybody help?

I tried it with this one:

    DELETE FROM todos WHERE todo_id IN
    (SELECT todo_id FROM
      (SELECT todo_id FROM todos
        WHERE list_id = :list_id
        AND is_checked = :is_checked
        order by created_at ASC LIMIT 20
      ) a
    )

But this deletes the oldest 20 records - that's not exactly what I want.

EDIT: This seems to be a working Query:

    $sql = "DELETE FROM todos WHERE todo_id IN
    (SELECT todo_id FROM
      (SELECT todo_id FROM todos
        WHERE list_id = :list_id
        AND is_checked = :is_checked
        order by created_at DESC LIMIT 15, 50
      ) a
    )";

(a second parameter in the LIMIT clause was needed. It needs to be bigger than the first parameter) I think the problem with this query is, that it needs to be executed on every update - that doesn't seem to be very performant. Any ideas how to make this more efficient?

i advice you to read [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) and provide example data and expected results as formatted text — Raymond Nijland, Dec 03 '18 at 16:42
"I want to build a query that checks on insert/update:" That would require MySQL [triggers](https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html) — Raymond Nijland, Dec 03 '18 at 16:43

Delete from Mysql table where datetime is older than datetime of another item

0 Answers0