CODEWITHSUNDEEP
sqlite
user435062
user435062

Reputation: 133

How to delete first few records from a table using SQLite query?

I want to delete first 10 records(i.e. records 1 to 10) from my table in SQLite if the record count is 110. Only 100 records I want in my table. If record count is more than 100 then those records should be deleted, as a result if I add new records then also only 100 records should be present. How could it be possible using SQLite query, please provide your suggestions. Thanks.

Upvotes: 4

Views: 18002

Answers (3)

omggs
omggs

Reputation: 1153

I gather you want to maintain 100 rows in your table by removing the first 'X' rows every time your table grows beyond 100 rows. Here is what you can do:

Delete from table_name where rowid IN (Select rowid from table_name limit X);

This will keep removing the first 'X' rows as and when your SQLite table grows.

Upvotes: 7

spacebiker
spacebiker

Reputation: 3875

First you have to do a select that returns the last 100 records in your table, then use the delete command to remove all ids not in the result, something like this:

DELETE FROM tbl_name WHERE NOT EXISTS in (SELECT id FROM tbl_name ORDER BY id LIMIT 100 );

Hope this helps!

Upvotes: 1

meticulousMisnomer
meticulousMisnomer

Reputation: 81

DELETE FROM {{ table }} LIMIT 0, 10

I'm not entirely sure if that's correct and I don't have the ability to check at this moment.

Upvotes: -4

Related Questions