Delete all but top n from database table in SQLite for java/android

Question

I am new in java but I am trying to code:

      SQLiteDatabase db = this.getWritableDatabase();
        //db.execSQL("delete from " + TABLE_BOOKMARKS + " where " + KEY_ID + "  not in (select " + KEY_ID + " from " + TABLE_BOOKMARKS + " order by " + KEY_ID + " limit 10)" );
        //db.execSQL("DELETE FROM " + TABLE_BOOKMARKS + " WHERE " + KEY_ID + " NOT IN (SELECT TOP 10 " + KEY_ID + " FROM " + TABLE_BOOKMARKS + ")" );

        db.execSQL("Delete From "+TABLE_BOOKMARKS+"  where "+ KEY_ID  + " not in (Select Top 10 "+ KEY_ID +" from "+ TABLE_BOOKMARKS + " order by " + KEY_ID +")");
        db.close(); // Closing database connection

you can see in the code, I have tried in 3 different ways but when I run it my application returns error and stops!

I got this error:

android.database.sqlite.SQLiteException: near "10": syntax error: , while compiling: Delete From bookmarks where id not in (Select Top 10 id from bookmarks order by id)

stackoverflow.com/questions/2728999/…

Sam CD
– Sam CD

2015-07-24 20:42:06 +00:00
Commented Jul 24, 2015 at 20:42 — Sam CD
– Sam CD, Commented Jul 24, 2015 at 20:42

Aiken · Accepted Answer · 2015-07-24 20:47:15Z

1

You can try

db.execSQL("Delete From "+TABLE_BOOKMARKS+"  where "+ KEY_ID  + " not in (Select "+ KEY_ID +" from "+ TABLE_BOOKMARKS + " order by " + KEY_ID +" limit 10)");

answered Jul 24, 2015 at 20:47

Aiken

886 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete all but top n from database table in SQLite for java/android

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related