Can't get table column to auto increment from preset value

Question

I want to make a column in my table auto-increment starting from the value 1001, not 1, but it's giving me an error when I try to set AUTO_INCREMENT = 1001.

I created my column like this: ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (id);

And then I tried adding auto-increment from 1001 like this: ALTER TABLE users COLUMN id AUTO_INCREMENT=1001;.

But it gave me the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COLUMN id AUTO_INCREMENT=1001' at line 1. And the value stayed at 1.

EDIT: I tried only ALTER TABLE clients AUTO_INCREMENT=1001. While it doesn't give me any error, the value of the column stays 1.

EDIT 2: It worked now after I deleted the rows and added them again.

https://stackoverflow.com/questions/970597/change-auto-increment-starting-number — Roland Starke, Mar 31 '18 at 09:28
changing the auto increment will not update your already inserted rows. — Roland Starke, Mar 31 '18 at 09:34

score 1 · Accepted Answer · answered Mar 31 '18 at 09:29

1

Try:

ALTER TABLE users AUTO_INCREMENT = 1001;

answered Mar 31 '18 at 09:29

AndrewShmig

4,843
6
39
68

This doesn't give me any error but the value stays at 1. It doesn't change it. – opportunityr Mar 31 '18 at 09:29

score 0 · Answer 2 · answered Mar 31 '18 at 09:30

0

Maybe you should try this out mysql> ALTER TABLE users AUTO_INCREMENT = 1001;

answered Mar 31 '18 at 09:30

joey

66
6

score 0 · Answer 3 · answered Mar 31 '18 at 09:31

0

There is no need to mention the column name, just add auto increment you will get that.

ALTER TABLE users AUTO_INCREMENT=1001

answered Mar 31 '18 at 09:31

Gulmuhammad Akbari

1,986
2
13
28

Can't get table column to auto increment from preset value

3 Answers3