Deleting specified number of rows from SQLite database

Question

I am trying to remove 6 rows from the database using the following statement but I get the error shown below.

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

Error:

Caused by: android.database.sqlite.SQLiteException: near "ORDER": syntax error: DELETE FROM tblname*

I tried reformatting the SQL in different ways but couldn't get it to work. What am I missing?

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 the _id, though.

Answer 2

The LIMIT and ORDER options for DELETE in sqlite are optional, and it appears they aren't enabled on Android. Borealid's SQL above will work fine even without the quotes.