Delete oldest records from database

I have a database with 1000 records. I am trying to create an S开发者_StackOverflow中文版QL 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.

If you use an auto-increment field, you can easily write this to delete the oldest 100 records:

DELETE FROM mytable WHERE id IN (SELECT id FROM mytable ORDER BY id ASC LIMIT 100)

Or, if no such field is present, use ROWID:

DELETE FROM mytable WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID ASC LIMIT 100)

Or, to leave only the latest 1000 records:

DELETE FROM mytable WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID DESC LIMIT -1 OFFSET 1000)

Assuming that your table has a Primary Key and a column with a timestamp indicating when the record was inserted), you can use a query along the lines of

delete from tableToDeleteFrom
where tablePK in 
(select tablePK 
from tableToDeleteFrom
where someThresholdDate <= @someThresholdDate)

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