Welcome to WebmasterWorld Guest from 54.146.240.181

Forum Moderators: open

Message Too Old, No Replies

MySQL update new column

Incremental number

     

Alternative Future

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

WebmasterWorld Senior Member 10+ Year Member



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

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

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)

WebmasterWorld Senior Member 10+ Year Member



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

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

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)

WebmasterWorld Senior Member 10+ Year Member



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
 

Featured Threads

Hot Threads This Week

Hot Threads This Month