SQLite Forum

How to reset auto increment on DB browser
Login

How to reset auto increment on DB browser

(1) By elfenliedtopfan5 on 2021-05-16 19:22:13 [link] [source]

Hello all i am trying my best to reset the auto-increment on DB browser but coming up with multiple santex errors,

ALTER TABLE User AUTOINCREMENT = 1

error log, ALTER TABLE User AUTOINCREMENT -- Result: near "AUTOINCREMENT": syntax error -- EXECUTING ALL IN 'SQL 1'

and

SET @num :=0;

UPDATE User SET UserID = @num := (@num+1);

ALTER TABLE User AUTOINCREMENT = 1;

error log SET -- Result: near "SET": syntax error

i have no idea why its not working i done work with phpmyadmin on sqlitedbs and those both worked fine but for some reason i cant seem to reset the id number back to 1 from db browser if anyone knows how to do this could you please assist

Kind Regards, Elfenliedtopfan5

(2) By tom (younique) on 2021-05-16 21:13:43 in reply to 1 [link] [source]

You cannot use ALTER TABLE for that. The autoincrement counter is stored in a separate table named "sqlite_sequence". You can modify the value there:

UPDATE sqlite_sequence SET seq=1 WHERE name="<table>"

(3) By Keith Medcalf (kmedcalf) on 2021-05-16 21:34:29 in reply to 2 [source]

That would be:

UPDATE sqlite_sequence SET seq=1 WHERE name='<table>'

In SQL double-quotes are for identifiers and single-quotes are for strings.

(4) By elfenliedtopfan5 on 2021-05-17 13:42:19 in reply to 3 [link] [source]

Thank you very much for quick reply this has resolved the issue :)

(5) By tom (younique) on 2021-05-17 20:59:37 in reply to 3 [link] [source]

Yepp, sorry about that. Didn't realize that

is a string in this case, not an identifier ;)