delete oldest records from database

Question

I have a database with 1000 records. I am trying to create a sql statement so if the number of records will grow, then the oldest records to be deleted. I am using sqlite, but I assume the usual sql syntax will fit here.

Thanks

Answer 1

if you use an autoincrement 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 rowidin (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 0 offset 1000)

Answer 2

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)