CODEWITHSUNDEEP
androidsqlite
donison24x7
donison24x7

Reputation: 304

How to reset/reuse deleted row id instead of MAX(id) in sqlite

I have a table in SQLite namely table_a. It's one of the column 'id' is set to auto increment. I have 5 rows so the ids are 0,1,2,3,4. then I deleted the rows with id 2 & 3. now when I add 3 rows its Id is auto generated as 5,6,7. But i want it should use the deleted row id i.e 2 and then use 3 then use 5. Any Idea how can this be done.

Upvotes: 2

Views: 684

Answers (1)

SMR
SMR

Reputation: 6736

this may work for you

SELECT id + 1 FROM TableName t1 WHERE NOT EXISTS (SELECT 1 FROM TableName t2 WHERE t2.id = t1.id + 1) limit 0,1

Upvotes: 1

Related Questions