Delete oldest records from database

user_66071 0
Viewed 34537

I have a database with 1000 records. I am trying to create an SQL statement so if the number of records grows above 1000, then the oldest records are deleted (i.e. the new records above 1000 'replace' the oldest records). I am using SQLite, but I assume the usual SQL syntax will fit here.

sqlite sql
3 Answers

For delete all records except the first record (min/max id) you can use:

SET @ls AS INT

SELECT @ls = MIN(id) FROM DATA

DELETE FROM DATA WHERE id <> @ls
user_29666070
Related