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)

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top