How do I delete all the rows but the first 2?

How do I delete all the rows in my database, but keep the first (oldest) 2 rows? Also, how do I reset the auto increment row back to row 3 after I delete all the rows?

Thank you.

 

You're going to have to use logic in the WHERE clause to prevent the 2 rows from being deleted. MySQL doesn't support putting an offset in a delete statement, so there's no inherit way of doing it.

DELETE FROM my_table WHERE id < second_to_last_id_value;

The easiest way to do everything you need is to make a tmp table, copy the 2 records to the tmp table, truncate the original table and then re-insert them into the original table. You should be able to create a stored procedure to do this fairly easily in one call.

Otherwise, after you perform the delete, you will need to update the primary key of the last 2 records, and then run an alter table or optimize table to reset the auto increment.

 

Hi ,
running those scripts would take care of it.

DELETE FROM test.table WHERE id > second_id_value;
ALTER TABLE test.table AUTO_INCREMENT = 3;

where 3 being the new value wanted

Or like jestep's mentioned, with the SP (stored procedure)

Thanks,

Sk

 

Okay. Thanks!

 

you can use the query delete from the table name where rownum>2;
it delete the rows only that are greater than the 2