MySQL: Delete all rows older than 30 days, but only if count more than one

deletegroup byMySQL

I need to cleanup my table of over 14.000.000 rows inside. I want to delete all entries, that are older than 30 days, but only if number of grouped items is larger than one. So, at least one item will stay.

Getting items and deleting them by date is not that problem. But it will delete all.

DELETE FROM parsed 
WHERE timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))

I thought something like that

SELECT *
FROM parsed
WHERE TIMESTAMP < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 8 DAY))
    AND (
        (
            SELECT COUNT(*)
            FROM parsed
            GROUP BY item_id
            ) > 1
        )

But this will not work. Thanks for help!

Best Answer