32

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.

sashoalm
  • 75,001
  • 122
  • 434
  • 781
phm
  • 1,160
  • 1
  • 17
  • 29
  • You might get more answers if you include some table schema details. – serg10 Sep 08 '10 at 11:29
  • You might want to provide more information. Like do you have a timestamp on the columns? Is the id a GUID or a auto increment? what is your goal to remove all older then X date or is there a limit of 1000 records? or what is the reason for deleting these records? We can better answer your question then. – Nathan Stanford Sep 08 '10 at 11:31
  • Oh and what is your primary key field? or do you even have one? – Nathan Stanford Sep 08 '10 at 11:32

3 Answers3

64

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)
sashoalm
  • 75,001
  • 122
  • 434
  • 781
Alex
  • 14,338
  • 5
  • 41
  • 59
  • 1
    Thanks, but I needed a ... limit -1 offset 1000 – Seven Apr 08 '13 at 18:50
  • I was thinking about using this also, but won't MySQL use the previously deleted IDs eventually so this won't work anymore? Since ID=1 will become available, a newer row will be created with ID=1 then will be deleted first..? – NaturalBornCamper Jun 26 '17 at 08:42
  • no. sql simply has a record of the highest index used in the table and adds one to it. – John Lord Jan 31 '19 at 17:47
  • will there not be an overflow at some point at when the highest index will be start again at 0? – deetz Nov 07 '19 at 09:09
  • For context, this is a SQLite question. SQLite rowid is supposed to always use a higher number than the currently existing rowids when creating a new row. If you use `AUTO INCREMENT` you get the stronger property of using a higher number than was ever used before. For the scenario as described here, the rowid property should suffice, but the rowid behavior I described is not guaranteed, it is just the current behavior. – ypnos Jan 22 '20 at 16:55
  • Would this also work? `DELETE FROM mytable ORDER BY id ASC LIMIT 100` – Andrew Jan 30 '20 at 18:22
  • @deetz This is a 64-bit integer. An overflow is unrealistic for most applications in the world. Applications that have such a high database traffic that this could become a problem won't be based on SQLite anyway. – ultimA Jun 19 '23 at 16:39
1

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)
StuartLC
  • 104,537
  • 17
  • 209
  • 285
-4

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
voromax
  • 3,369
  • 2
  • 30
  • 53
Hagai
  • 1