Search code examples
androidsqlitesql-delete

Delete few rows from db table

Right now in my code I check if row count is greater than 100 then I delete all the rows by using context.getContentResolver().delete( tablename, null, null );.

But I want to delete rows from 1 to 80 and after deleting those rows, offset( rename ) the last left over rows from 80 to 100 -> 1 to 20 and if I add any rows in that table the primary key ID should start from 21.

Can someone help me with this?

Solution

  • Assuming the primary key of your table is called _id and is set to autoincrement. Delete the 80 rows you don't want:

    String query = "DELETE FROM " + tablename + " WHERE _id <= 80";
context.getContentResolver().execSQL(query);

    For those rows that remain (that are ids 81 - 100), update the id by subtracting 80 so they'll then go from 1 - 20:

    query = "UPDATE " + tablename + " SET _id = _id - 80";
context.getContentResolver().execSQL(query);

    Update the entry in the sqlite_sequence table for this table. Set seq to that of the last id on the table:

    query = "UPDATE sqlite_sequence SET seq = 20 WHERE name = '" + tablename + "'";
context.getContentResolver().execSQL(query);

    When you next insert, _id will continue from 21.