Removing a specified number of rows from a SQLite database

Question

I try to delete 6 rows from the database using the following statement, but I get the error below

getWritableDatabase (). execSQL ("DELETE FROM tblname ORDER BY _id ASC LIMIT 6;");

Error: Called: android.database.sqlite.SQLiteException: next to "ORDER": syntax error: DELETE from tblname

I tried to reformat SQL differently, but I could not get it to work. What am I missing? Many thanks for your help.

Answer 1

DELETE FROM tblname WHERE `_id` IN (SELECT `_id` FROM tblname ORDER BY `_id` ASC LIMIT 6) 

I think your problem may have been quoting _id .

Answer 2

The LIMIT and ORDER parameters for DELETE in sqlite are optional, and it seems they are not enabled on Android. Borealid SQL above will work fine even without quotes.