homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

MySQL update new column
Incremental number
Alternative Future

 7:54 pm on Mar 19, 2010 (gmt 0)

Greetings to the forum

I have an existing table with several hundred rows, I wish to add another column and call it id, I wish this new column to add an incremental number to all new column id's starting at 0 to ###.
Is this possible?
And if so how would I go about doing this?

With thanks




 7:56 pm on Mar 19, 2010 (gmt 0)

you could just alter table and add an int column that is auto increment can't you?

frankly I don't think I have ever added an auto increment to an existing table

Alternative Future

 8:06 pm on Mar 19, 2010 (gmt 0)

Hi jatar_k

Thanks for taking the time out to answer my question.

Yes, this would add a column that would auto-increment for NEW rows. However, I wish my existing rows to also be assigned auto-generated IDs.

From what i have read on the mysql site:
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows.

Would running the alter command from your post above update the existing rows with an incremental number?




 8:26 pm on Mar 19, 2010 (gmt 0)

that's where I'm not sure, you could populate a test table and try, as long as you give no default it should do it

the other option would be to add an int col, then make a script to enter the values, then alter it to auto increment and set it to the next value

Alternative Future

 8:52 pm on Mar 19, 2010 (gmt 0)

It worked :)

alter table my_table add id int not null auto_increment primary key

This will work only if there isnt a primary key defined already :)

Thanks again jatar_k


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved