CODEWITHSUNDEEP
mysqlsql
Chad Watkins
Chad Watkins

Reputation: 561

increment value in mysql after the table has been created

I would like to change the value in the second column to increment and also change the current NULL values to start using that incrementing system that begins at a value other than 1. I do see I can use

ALTER TABLE t2 AUTO_INCREMENT = value;

to change the table to start incrementing at the number I want it to but the values them selves are still null how do I change my table from this:

| Id | OtherNumber | Name |
| -- |:-----------:| ----:|
| 1  | NULL        | Bob  |
| 2  | NULL        | Susan|
| 3  | NULL        | Bill |

to this

| Id | OtherNumber | Name |
| -- |:-----------:| ----:|
| 1  | 7           | Bob  |
| 2  | 8           | Susan|
| 3  | 9           | Bill |

How would I make the change if the table is in the hundreds of rows? Thanks

Upvotes: 2

Views: 116

Answers (2)

Will Carr
Will Carr

Reputation: 29

If you make "OtherNumber" the primary key when you alter it to auto increment, mysql should fill in the values for you

To change the starting number, it seems you need to use the old primary id column to get the "first" row in the table. Mysql will then pick this value to start the auto increment from.

update t2 set OtherNumber = value where t2.Id = 1;
ALTER TABLE `test`.`new_table` 
CHANGE COLUMN `OtherNumber` `OtherNumber` INT(11) NOT NULL AUTO_INCREMENT ,
ADD PRIMARY KEY (`OtherNumber`);

The nulls should be filled in with your auto increment setting.

You can later change the primary key back to the id column.

Upvotes: 2

Drew
Drew

Reputation: 24959

how about if you leave Id as auto-increment, drop the OtherNumber column, and when you do a select, have OtherNumber be a derived column=Id+6

There, maybe some other Answers will come along. Others could be better.

Upvotes: 1

Related Questions