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 [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 [link] [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