Edin Omeragic Edin Omeragic - 7 months ago 28
SQL Question

Re-assign id numbers in sorted table

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:

| id | name | new_id |



| 1 | C | 1 |



| 2 | A | 2 |



| 3 | B | 3 |




Now we sort it by name in ASC mode:


| id | name | new_id |



| 2 | A | 2 |



| 3 | B | 3 |



| 1 | C | 1 |




Now we reassign id's in that order:


| id | name | new_id |



| 1 | A | 2 |



| 2 | B | 3 |



| 3 | C | 1 |



I use this code:

set @row_num = 0; SELECT @row_num := @row_num + 1 as `row_number`,`id`,`name`,`new_id` FROM `test` ORDER BY `name`;


it add new row row_number and add numbers in order that i need for id to be. How to do this.

Answer

You can do this using update:

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.

Comments