Menu
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.
558
Expand Databases, right-click the database from which to delete the file, and then click Properties. Select the Files page. In the Database files grid, select the file to delete and then click Remove. Click OK.
So the delete statement is used to delete rows from a table using the where clause to select only the rows to be deleted. Always use a unique identifier to locate the rows that you need to delete. To delete all the rows in a table, always use TRUNCATE TABLE.
Removing most of the rows in a table with delete is a slow process. One that gets slower the more data you're wiping. Add in other user activity such as updates that could block it and deleting millions of rows could take minutes or hours to complete.
If you need to remove 10 million rows and have 1 GB of log space available use Delete TOP(10000) From dbo. myTable (with your select clause) and keep running it till there are no more rows to delete.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
