Add a PRIMARY KEY that auto increments starting with 1001

Question

This is the code I used:

ALTER TABLE `event_all_copy` 
ADD `id` INT(5) AUTO_INCREMENT = 1001 PRIMARY KEY;

I get the following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO INCREMENT = 1001 PRIMARY KEY' at line 1

The code will run fine if I leave off the AUTO_INCREMENT = 1001. If I add id then alter it:

ALTER TABLE event_all_copy AUTO_INCREMENT = 1001; 

it doesn't change the existing values.

Answer 1

You need to alter the table telling it to start from 1000:

ALTER TABLE `event_all_copy` AUTO_INCREMENT = 1000;

So your query will be:

ALTER TABLE `event_all_copy` ADD `id` INT(5) PRIMARY KEY;
ALTER TABLE `event_all_copy` AUTO_INCREMENT = 1000;

You cannot have AUTO_INCREMENT value in the ADD query. The two queries should be two separate ones. To change the existing values, that's easy. You can do:

UPDATE `event_all_copy` SET `id` = `id` + 1000;

The AUTO_INCREMENT updated value will be set only for the newly entered records.

Answer 2

Try this after defining the table structure:

ALTER TABLE tablename AUTO_INCREMENT = 1000;