i'm looking for solution to my problem.
I need to reassign id's in that order in which that table is sorted.
To be more clear here is the how table look:
Now we sort it by name in ASC mode:
Now we reassign id's in that order:
set @row_num = 0; SELECT @row_num := @row_num + 1 as `row_number`,`id`,`name`,`new_id` FROM `test` ORDER BY `name`;
You can do this using
set @rn := 0; update t set new_id = (@rn := @rn + 1) order by name;
You don't really need to use
new_id. If you want to re-set
id, just set that instead.