Increment all values in mysql table

Question

Lets say I have

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | YES  | UNI | NULL    |       |
| isbn        | varchar(20)  | NO   |     | NULL    |       |
| title       | varchar(200) | YES  |     | NULL    |       |
| author      | varchar(200) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

The primary key was isbn (which I have now removed and I have added id (i will make primary soon) before I can do this i need to make sure that all id's are unique (so I want to update all values to increment starting at 1;

I have tried the following but it isn't working any suggestions?

SET @bookid = 1; UPDATE books SET `id` = @bookid + 1;

I think it would just be easier to create a new auto-inc table and `INSERT ... SELECT` — Kermit, Jan 29 '13 at 19:53

Walls · Accepted Answer · 2013-01-29T20:05:44.173

2

Use this:

ALTER TABLE books AUTO_INCREMENT=1;

or if you haven't already id column, also add it

ALTER TABLE books ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD INDEX (id);

edited Jan 29 '13 at 20:05

answered Jan 29 '13 at 19:56

Walls

3,972
6
37
52

Thanks! I ran ALTER TABLE books DROP `id`; ALTER TABLE books ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id); – Lizard Jan 29 '13 at 20:03

score 0 · Answer 2 · answered Jan 29 '13 at 20:01

0

i think you are looking for something like that

   SET @bookid = 1; 
   UPDATE books SET `id` =`id`+ @bookid ;

your query wll always give id = 2

answered Jan 29 '13 at 20:01

echo_Me

37,078
5
58
78

Increment all values in mysql table

2 Answers2