This technique is useful if, for example, “x” errant rows were recently added to a table and you not only want to programmatically delete those rows, but also reuse their auto increment values:

CREATE TEMPORARY TABLE tbl_ad_hoc AS
    (SELECT *
     FROM tbl_gold_prices
     ORDER BY barID ASC #Where: 'barID' is our auto increment primary key
     LIMIT n );

TRUNCATE TABLE tbl_gold_prices;

INSERT INTO tbl_gold_prices SELECT * FROM tbl_ad_hoc;

Problem Statement

We have a 40 row table and recently added 10 rows that were in error. How can we programmatically (i.e., by code and not manually) remove these 10 new rows but also preserve a clean sequence of auto increment values for our index and primary key?

Solution

1. Create a temporary table. This will be used to store our “good” rows
2. Sort our main table by primary key from lowest value to highest (i.e., in ascending order)
3. Use LIMIT to select only the first 30 rows (remember, the last 10 rows were errant and we don’t want these). The ASC command in step 2 will ensure that the latest 10 rows are at the bottom of the sort
4. Remove all rows from our main table using truncate command. All table and column properties will be retained. Only the rows will be deleted
5. Finally, copy the “good” rows into our truncated main table

Caveats and Items to Consider

NOTE: Replace the “n” in the above SQL with the number of rows you want to keep. For example, to keep the top 30 rows and delete everything else, use LIMIT 30.

A TEMPORARY table is visible only to the current connection. It is dropped automatically when the connection is closed.

TRUNCATE does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.

---

References

1. Stack Overflow: How LIMIT Works
2. MySQL Temporary Tables
3. Stack Overflow: Efficiently Remove All But Top n Rows
4. MySQl Truncate Table Man docs