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

Posted on 2011-05-07
Last Modified: 2012-05-11

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.
Question by:NewWebDesigner
    LVL 59

    Accepted Solution

    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.
    LVL 15

    Assisted Solution

    by:Jagadishwor Dulal
    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

    LVL 107

    Assisted Solution

    by:Ray Paseur
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now