android.database.sqlite.SQLiteException: near "ORDER": syntax error (code 1): ,
android, sqlite
Solution
`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.
Problem
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