2

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?

TechnoCrat
  • 2,055
  • 4
  • 23
  • 35
  • possible duplicate of [Can I have an MySQL database auto-assign a value to a column after an insert statement?](http://stackoverflow.com/questions/6614982/can-i-have-an-mysql-database-auto-assign-a-value-to-a-column-after-an-insert-sta) – Andrew Jan 29 '13 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 Jan 29 '13 at 12:14

3 Answers3

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;
Anthony
  • 12,407
  • 12
  • 64
  • 88
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');
Praveen Kumar Purushothaman
  • 164,888
  • 24
  • 203
  • 252
1

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

id int NOT NULL AUTO_INCREMENT,