homepage Welcome to WebmasterWorld Guest from 54.226.18.74
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL update new column
Incremental number
Alternative Future




msg:4101214
 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

-Gs

 

jatar_k




msg:4101215
 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




msg:4101222
 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?

Thanks

-gs

jatar_k




msg:4101238
 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




msg:4101273
 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

-gs

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