Reset ID Field

[graham23s]

Hi Guys,

 

my database unique id starts at: 650 - 950 for example! is there a way i can reset it to: 1 - 300?

 

cheers

 

Graham

[alexweber15]

SQL:

ALTER TABLE tablename AUTO_INCREMENT = 1

This will reset the next auto increment value to current largest value in the auto increment column + 1 (so executing this on an empty table will make the first id be 1 or whatever you specify)

 

Now to solve your problem, the easy is just DROP the auto_increment column and recreate it

 

its a hack i guess but sure as hell beats doing it any other way...

 

EDIT: note you can also do this to force the next auto increment id to a certain value (not sure how this behaves if you specify a taken value, but im pretty sure it'll work like the above function and get the largest auto inc + 1

SET insert_id = 1;

[fenway]

EDIT: note you can also do this to force the next auto increment id to a certain value (not sure how this behaves if you specify a taken value, but im pretty sure it'll work like the above function and get the largest auto inc + 1

SET insert_id = 1;

I've never heard of that.

 

If you're starting again fresh, you can use TRUNCATE to empty the table properly, instead of DELETE.

[alexweber15]

EDIT: note you can also do this to force the next auto increment id to a certain value (not sure how this behaves if you specify a taken value, but im pretty sure it'll work like the above function and get the largest auto inc + 1

SET insert_id = 1;

I've never heard of that.

 

If you're starting again fresh, you can use TRUNCATE to empty the table properly, instead of DELETE.

 

that's the point:

 

delete only the id column, not the entire table!!  then re-create it as auto-inc (and possibly reset the counter by using ALTER TABLE) and it will assign new numbers.

 

and about "SET insert_id" look it up its in the manual