What command can I use to rearrange the ID of all items in a table?
The name of the table is produtos and the column is id .
Currently they look like this:
id: 1 - 3 - 7 - 8 - 9 - 11 ...
That is, they are not in the correct sequence.
I need to leave them this way:
id : 1 - 2 - 3 - 4 - 5 - 6 - 7 ...
But it is important that he follow the correct sequence not to mix, eg:
1 = 1
3 = 2
7 = 3
8 = 4
9 = 5
11 = 6
The server is MySQL.
To regularize your base, you need to update the data already registered, as well suggested @sveen . p>
SET @count = 0;
UPDATE 'tabela' SET 'tabela'.'id' = @count:= @count + 1;
Once you've done this, you'll need to update your " counter " so that the new inserted items follow from the last index .
ALTER TABLE tablename AUTO_INCREMENT = (SELECT MAX(id) FROM tabela) + 1
Detail: If there is a possibility of a physical deletion (delete the records from the base), I suggest you do not use auto_increment , but a numeric column that can be controlled more easily via code.