Adding a new column in MySQL that adds a value for each row in the table


I have a table that has about 70 values,  I want to add another column that will contain an auto incrementing number for each row that exists.  So the value of this column, lets call it display order, it '1' in the first row, '2' in the second row, and '70' in the last row.  How can I do this programmatically so that I don't have to enter in all 70 values for this column?

If you could tell me how to do this in phpMyAdmin, as well as in SQL,that would be a plus.
Who is Participating?
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
You can only have one AUTO_INCREMENT field on a table and it needs to be primary key, but here it is:
ALTER TABLE `your table name` 

Open in new window

If you need just a regular column, then you can use similar syntax for altering table as above and then use rownum trick shown in my article here to get a row number for each existing row to use and update the new columns value.

You will have to maintain the column's incrementing manually from that point unless you can make it the AUTO_INCREMENT key.  Just note that can have gaps if you delete rows, etc.
Jagadishwor DulalConnect With a Mentor Braces MediaCommented:
Using phpmyadmin:
Select your database and select your table
click on structure
Where you can see your complete table stracture
and at the end of the stracture you can see Add input box and default 1 values
There are 3 radios explain where you have to add another field At end of Table, At beginning of Table or after some field (There will be field list) Now you can choose at beginning of table
click on go.
The field property table will be shown
Enter your field name choose data type may be int or bigint.
and in extra drop down choose auto_increment.
Select Primary key radio and click on go. If there no any other primary key and you are success.

For your sql :


Open in new window

Ray PaseurConnect With a Mentor Commented:
Maybe I am just paranoid about these things, but I would not use phpMyAdmin to do the work.  Instead I would write a little PHP script that copied the data from this table into a new table (one that contained the AUTO_INCREMENT key).  Then I would use phpMyAdmin to look at the work product and make sure the copy had worked correctly.  Next, I would rename the old table to indicate it was old, and I would rename the new table to take the name of the other, old table.  Then I would test my work.  If all goes well, you can DROP the old table.  If there are any hiccups along the way, you still have the old table and can correct the problems instantly by simply renaming the old table back to its original name.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.