Thursday, August 27, 2015

Android and SQLite - Delete all but top n rows from a database table

After some testing, I have found that in order to delete all but top n rows from an Android SQLite database , you should use SQLiteDatabase::execSQL instead of SQLiteDatabase::rawQuery.

 SQLiteDatabase::execSQL works as expected, and deletes records below limit. Function rawQuery produces no error, but still it does not delete the rows in table.

So, here is a sample code to delete all but n top rows in Android Java / SQLite:

[code]
public void DeleteAllButTopNRows(SQLiteDatabase db, int limit)
{

//this line works:
  db.execSQL("DELETE FROM table WHERE idField NOT IN (SELECT idField FROM table ORDER BY sortField DESC LIMIT " + limit) ;


//this line does not work:
  db.rawQuery("DELETE FROM table WHERE idField NOT IN (SELECT idField FROM table ORDER BY sortField DESC LIMIT " + limit, null) ;



}

[/code]

Hope this helps!

No comments:

Post a Comment

Ubuntu 12.04, 14.04, 16.04 - auto start an app or script before login

To run a command or application at startup, even before the user has logged in, you can use this file: /etc/rc.local The commands entered...