SQL: Delete All Rows Except Last X
If you want to delete all rows from a table but exclude the last X rows (in this case 20), you can use this SQL statement:
DELETE FROM `my_table`
WHERE id NOT IN(
SELECT
id FROM (
SELECT
id FROM `table`
ORDER BY
id DESC
LIMIT 20 -- number of records to keep
) subquery);
Another option would be to use this query to avoid a large list of IDs:
DELETE FROM `my_table`
WHERE id <= (
SELECT
id
FROM (
SELECT
id
FROM
`my_table`
ORDER BY
id DESC
LIMIT 1 OFFSET 20 -- number of records to keep
) subquery)
Related Posts
- Manage MySQL Databases and Users from the Command Line
- Install Umami Analytics with Let's Encrypt HTTPS
- Debug WordPress SQL Queries
- Remove Featured Image From All Posts and Pages
- Force Easy Digital Downloads Updater to Check for Updates
- Fix "Failed to get metadata: Local: Broker transport failure" When Updating Sentry
- Fix Laravel Forge Site Creation Problems
- Reset MySQL Root Password From The Command Line