[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

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

Hello,

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.
Thanks
0
NewWebDesigner
Asked:
NewWebDesigner
3 Solutions
 
Kevin CrossChief 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` 
ADD id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY;

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.

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html

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.
0
 
Jagadishwor DulalBraces 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 :

ALTER TABLE `tablename` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

Open in new window

0
 
Ray PaseurCommented:
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.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now