android.database.sqlite.SQLiteException: near "ORDER": syntax error (code 1): ,

Question

I keep getting this error. I'm sure it's a simple syntax error. Does anyone see it? I been debugging for ~30min and can't seem to find it.

query

DELETE FROM SuccessfulCalls ORDER BY id DESC LIMIT 2

Actual code to insert to db.

SQLiteHelper sqLiteHelper     = ApplicationClass.getSqLiteHelper();
SQLiteDatabase db             = sqLiteHelper.getWritableDatabase();
String query = "DELETE FROM " + sqLiteHelper.SUCCESSFULCALLSTABLE + " ORDER BY " + sqLiteHelper.SUCCESSFULID + " DESC LIMIT " + Constants.NUMBEROFCALLSTODELETE ;
db.rawQuery(query, null);
db.close();

UPDATE I have tried this query, but for some reason it does not delete the rows from my database.

DELETE FROM SuccessfulCalls WHERE id IN ( SELECT id FROM SuccessfulCalls ORDER BY id DESC LIMIT 5 );

But I know the query is getting executed because when I try. It deletes my whole table. DELETE FROM SuccessfulCalls

UPDATE I was setting up my primary key wrong. For sql lite to autoincrement one's primary key they need INTEGER PRIMARY KEY not int PRIMARY KEY

So, did you use rawQuery() or execSQL() for the updated query? — laalto

laalto laalto · Accepted Answer · 2014-05-29T15:24:57

ORDER BY and LIMIT are not syntactically allowed with a DELETE query unless sqlite is built with SQLITE_ENABLE_UPDATE_DELETE_LIMIT which is not the case on Android.

If you want to delete the two highest id rows, use

DELETE FROM SuccessfullCalls WHERE id IN (SELECT id FROM SuccessfulCalls ORDER BY id DESC LIMIT 2);

Use execSQL() for such queries and not rawQuery() to get the SQL actually run.

android.database.sqlite.SQLiteException: near "ORDER": syntax error (code 1): ,

2 Answers