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

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]

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]

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]

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]

Yepp, sorry about that. Didn't realize that <table> is a string in this case, not an identifier ;)