Have mysql re-allocate ids in a continuous range

Question

I have an existing database in mysql. One of my tables has discontinuous ids. I would like to modify the ids of the table so that they go from 1 to num-of-rows.

This particular tables does not happen to have incoming references, so the ids can be changed without modifying other tables.

The reason I want to do that is that I want to process the data with a tool I am writing and if the ids are continuous then many things will be simpler.

score 2 · Accepted Answer · answered Mar 04 '10 at 11:46

2

SET @r := 0;
UPDATE  mytable
SET     id = (@r := @r + 1)

answered Mar 04 '10 at 11:46

Quassnoi

413,100
91
616
614

Have mysql re-allocate ids in a continuous range

1 Answers1