Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Updatre table with auto-increment

    I have an existing table with a blank column and want update the column with numbers beginning with 1001 and auto-increment from there.

    Pointers would be welcome

    Nick

  2. #2
    Join Date
    Feb 2012
    Posts
    44
    here example code

    Code:
    INSERT into example (column) 
      SELECT max(column) + 1 
      FROM example

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If I were you Id probably want to implement this as a stored procedure "inside" the DB.

    However its perfectly possible to do this "outside" the db in your application front end. the only real problems that occur are if its a multi user system AND you expect users to be attempting to insert rows concurrently, which if you are not certain then assume that you need to handle such contention

    for a front end:-
    gather all data, prepare to write the new row
    secure a page/table lock
    find the next number
    write it
    release the page/table lock

    cynically you can use a more brute force approach (assumign that your sequential number has a unique index constraint and/or is the primary key
    gather all data, prepare to write the new row
    find the next number
    write it
    ..if the system rejects the insert (on a duplicate key error) then get a new number until it does accept the insert
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2009
    Posts
    124
    This inserts a new row, I want to update all the rows with sequential numbers

    WHERE column IS NULL


    Quote Originally Posted by clob View Post
    here example code

    Code:
    INSERT into example (column) 
      SELECT max(column) + 1 
      FROM example

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If there is a column or group of columns that are unique (no duplicates in the rows that you have now), then this is pretty easy. Is there a way to uniquely identify your rows now?

    Depending on what version of MySQL and which storage engine you are using, there may be other choices that don't depend on a way to uniquely identify rows. Which version and storage engine are you using?

    Help me help you... Please answer these questions.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2009
    Posts
    124
    Yes the id_run, it's the raceNum_run I want to populate. However not all the id_run records have paid so so they don't get a number. so "WHERE AmtPaid_run>1"

    CREATE TABLE `user_run` (
    `id_run` int(11) NOT NULL AUTO_INCREMENT,
    `raceNum_run` int(11) DEFAULT NULL,
    `fname_run` varchar(25) DEFAULT NULL,
    `lname_run` varchar(35) DEFAULT NULL,
    `email_run` varchar(75) DEFAULT '',
    `password_run` varchar(25) DEFAULT NULL,
    `pass_run` varchar(100) DEFAULT NULL,
    `gender_run` varchar(35) DEFAULT NULL,
    `phone_run` varchar(35) DEFAULT NULL,
    `address_run` varchar(35) DEFAULT NULL,
    `city_run` varchar(25) DEFAULT NULL,
    `state_run` varchar(25) DEFAULT NULL,
    `zip_run` varchar(15) DEFAULT NULL,
    `age_run` varchar(25) DEFAULT NULL,
    `event_run` varchar(35) DEFAULT NULL,
    `t_shirt_run` varchar(24) DEFAULT NULL,
    `t_size_run` varchar(35) DEFAULT NULL,
    `cost_run` decimal(8,2) DEFAULT NULL,
    `AmtPaid_run` decimal(8,2) DEFAULT NULL,
    `paid_run` decimal(8,2) DEFAULT NULL,
    `time_run` time DEFAULT NULL,
    `comment_run` text,
    `cdate_run` datetime DEFAULT NULL,
    `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id_run`)
    ) ENGINE=MyISAM AUTO_INCREMENT=250 DEFAULT CHARSET=latin1;

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    MyISAM doesn't allow any of the tricks that I know to work, so that won't help me to help you.

    The id_run intrigues me... If it is NOT NULL, how do you deal with people that have not paid? It can't be NULL, it can't be an identifier value. What the heck is it's value before they have paid?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    this might work as a kludge workaround, dunnot havn't tried it, but y'never know
    create a new (temporary table)
    run an insert query which takes the current tables PK and inserts that into the new table

    modify the temporary table to add an autoincrement column (you can specify the start value for the autoincrement column, but you dont' need to

    then update the original table using a join on the PK of the old table

    as I say its nasty, ist kludgey but it should work
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you have the disk to make a second table, just do the INSERT then rename the tables!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •