CODEWITHSUNDEEP
sqlsqlite
phm
phm

Reputation: 1170

Delete oldest records from database

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.

Upvotes: 32

Views: 41520

Answers (3)

Hagai
Hagai

Reputation: 1

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

Upvotes: -5

Alex
Alex

Reputation: 14618

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)

Upvotes: 64

StuartLC
StuartLC

Reputation: 107247

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)

Upvotes: 1

Related Questions