Mysql update id

Question

Is there a way to update all id's in a table to restart from 1.

I have :

id  |  name
-------------
 3  | whatever
 23 | whatever
 31 | whatever
 13 | whatever
 45 | whatever

I want :

id  |  name
-------------
 1  | whatever
 2  | whatever
 3  | whatever
 4  | whatever
 5  | whatever

thank you

Does any other table reference the `id` column? If so, you'll have to alter those too — ʰᵈˑ, Feb 10 '14 at 15:56
See this: http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key — Ryan, Feb 10 '14 at 15:56
..in particular, this answer: http://stackoverflow.com/a/5437720/1612146 — George, Feb 10 '14 at 15:58
Without trying to sound rude, while possible, it's one of the worst ideas one can have ever and if it EVER happens that you need or even WANT something like that, MySQL or any RDBMS might not be the storage layer you require. — N.B., Feb 10 '14 at 15:59
@Lorenzo You may also read this: http://stackoverflow.com/q/13643938/447489 <- why it might be a bad idea... — fancyPants, Mar 25 '14 at 09:03

score 1 · Answer 1 · answered Feb 10 '14 at 16:40

Fully agree with the above that doing this is a bad idea, but if you really want to do it another alternative method:-

UPDATE some_table
INNER JOIN 
(
    SELECT a.id, COUNT(b.id) AS seq
    FROM some_table a
    INNER JOIN some_table b
    ON b.id <= a.id
    GROUP BY a.id
) Sub1
ON some_table.id = Sub1.id
SET some_table.id = seq

score 0 · Answer 2 · answered Feb 10 '14 at 15:59

IHMO, This is a dangeros operation.

The first, you must be sure your ID haven't foreign key to point them.

The second, only with name field you can't rearrange your ID because the name is the same for all rows.

So you must obtain another field to discriminate your cicle.

If you pass all the previous steps, you can write a query to update your ID. In MySql you can't write an update with a subquery to point at the same table so you must to use a temporary table where you store a lookup (OLD_ID, NEW_ID).

Tell me if it's OK or you need further information.

Mysql update id

2 Answers2