How to assign sequential value to one column in mysql database?

Question

I have one table like this

enter image description here

and I want to convert it into table like this

enter image description here

Basically, I want to assign sequential value to column id. Is there any query for that? Also what are the equivalents for rownum in mysql?

possible duplicate of Can I have an MySQL database auto-assign a value to a column after an insert statement? — Andrew
– Andrew, Commented Jan 29, 2013 at 11:55
Actually I want to do it on existing rows in an existing table. So I assume that this question is different. — TechnoCrat
– TechnoCrat, Commented Jan 29, 2013 at 12:14

Anthony · Accepted Answer · 2013-01-29 11:55:29Z

2

You need to use AUTO_INCREMENT

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

answered Jan 29, 2013 at 11:55

Anthony

12.9k13 gold badges68 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Praveen Kumar Purushothaman · Accepted Answer · 2013-01-29 11:55:36Z

1

You can do it in one way. Make the id column as PRIMARY KEY AUTO INCREMENT and then change the data values of all the 1s with null and then try inserting.

CREATE TABLE `people` (
     `id` INT NOT NULL AUTO_INCREMENT,
     `name` CHAR(30) NOT NULL,
     `profession` CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=InnoDB;

And then the SQL like:

INSERT INTO `people` VALUES
(null, 'John', 'Engineer'),
(null, 'Ronny', 'Doctor'),
(null, 'Monty', 'Engineer');

answered Jan 29, 2013 at 11:55

Praveen Kumar Purushothaman

167k27 gold badges215 silver badges261 bronze badges

3 Comments

TechnoCrat Over a year ago

is it possible to do it in existing table?

TechnoCrat Over a year ago

can you please tell me how to do that?

Praveen Kumar Purushothaman Over a year ago

You just need to generate the SQL and replace the same values using a text editor. As simple as that. But do you have any code with you?

user2021438 · Accepted Answer · 2013-01-29 11:56:03Z

1

You would do this by creating a PRIMARY KEY and giving it an AUTO_INCREMENT, like this

id int NOT NULL AUTO_INCREMENT,

answered Jan 29, 2013 at 11:56

user2021438

Collectives™ on Stack Overflow

How to assign sequential value to one column in mysql database?

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related