Is there a way to reset database autoincrement???

narked

If my ids are automatically assigned by an autoincrement in the database is there a way to reset this?
to say the database autoincrement to go back to 0 or a specific value??

my db looks like this:
id name
id= autoincrement, name = string

I want to insert values to the database just with the name, without the id, the id I want to be assigned automatically. the problem is that if I erase a lot of names and add new ones I might have only 3 names and their ids are (151, 152...!) so that why I need a way to reset the ids. or to say: ok, autoincrement go bacvk to 5!

thanks

andymoo

ALTER TABLE $table AUTO_INCREMENT=$num;

might be worth a try - picked it up from experts-exchange after a quick google, had the same question not so long back after manually adding an id of 100000 to a table I was using then I wondered why the next addition was 100001

hth

Drakla

You could do something like this:

Create Table tempTable(id int not null auto_increment, name varchar(50), primary key(id) );

insert into tempTable (name) Select name from originalTable;

drop table originalTable;

rename table tempTable to originalTable;

That will start your ID's from 1 again. Thing is you're really using auto increment values for a reason - to create unique ID's for your entries, so resetting them is essentially bad practise.

narked

Its kind of strange but for this project Im working on I need to be able to change the autosum value to any number I want.

I think the: ALTER TABLE $table AUTO_INCREMENT=$num; is going to work perfect but im wondering whats the correct syntax for using it..?

something like:

$sql = "ALTER TABLE $table AUTO_INCREMENT=$num;"
$result = $db->query($sql);

or maybe something like:

$sql = "ALTER TABLE $table AUTO_INCREMENT=$num;"
$result = mysql_query($sql);

thanks for the help

Drakla

I've just done some experiments with the setting of the auto_increment value, and it's fine if you move it higher, but doesn't change at all if you move it lower so you might have to use the temp table method if it doesn't work for you.

Your syntax for using it in the query is spot on.

If it's easy to create the Id's for the columns yourself you could just bin the auto increment altogether.

narked

well, Ill need to use the temp table method. but my table is really more complicated than id and name, I just gave that as an example so it is goin to be more difficult.

if I have more columns to copy?
for exaple name and adress.. is this code good?:

insert_into_tempTable(name, adress)Select_name, adress_from_originalTable;

and the last question, by using the insert and select from commands are you copying ALL of the rows from one table to another?

Thanks man

Drakla

Yup, using the select in the insert like in the example will use all the rows, but you can add any of the normal select conditions or limits to the query - eg to take records 50 to 100 of a result set, or to take people whose name starts with S do this kind of thing:

insert into tempTable (name, address, phoneNumber) Select employeeName, homeAddress, homePhone from employees LIMIT 50,100;

or

insert into tempTable (name, address, phoneNumber) Select employeeName, homeAddress, homePhone from employees WHERE employeeName LIKE 'S%';

Wah - it's 4am again, night all.