CODEWITHSUNDEEP
androidsqlite
nexus490
nexus490

Reputation: 817

Resetting Autoincrement in Android SQLite

I have this method which will remove all rows from a table but I also want it to reset the autoincrement so that when a new row is added it will start again. The SQL statement I'm using isn't working due to certain columns not existing. Am I doing it right?

private void rmvAll() {

    SQLiteDatabase db = appts.getWritableDatabase();
    db.delete(TABLE_NAME, null, null);
    db.execSQL("DELETE FROM SQLITE_SEQUENCE WHERE NAME = " + TABLE_NAME);

}

Upvotes: 11

Views: 14284

Answers (4)

Reza Nahardani
Reza Nahardani

Reputation: 21

I was trying too hard.

Finally, I got this answer code...

Book.deleteAll(Book.class);
book=new Book(user, pass);
book.setId(Long.valueOf(book.listAll(Book.class).size()));
book.save();

this code work like delete and recreating the table.and reset id.

good luck

Upvotes: 1

Akhilesh Dhar Dubey
Akhilesh Dhar Dubey

Reputation: 2148

you can also use delete() method of SQLiteDatabase, like this

db.delete("SQLITE_SEQUENCE","NAME = ?",new String[]{TABLE_NAME});

Upvotes: 0

Trisped
Trisped

Reputation: 6007

Building on dldnh's answer:

The following query will set seq to the largest value in the col identity column in the Tbl table, so there is no risk of violating constraints.

UPDATE sqlite_sequence SET seq = (SELECT MAX(col) FROM Tbl) WHERE name="Tbl"

Upvotes: 4

dldnh
dldnh

Reputation: 8961

you'll need single quotes around your table name, i.e.

db.execSQL("DELETE FROM SQLITE_SEQUENCE WHERE NAME = '" + TABLE_NAME + "'");

Upvotes: 25

Related Questions