Today I came across the need to renumber a column value in a database. I had a table with a column indicating an order of certain rows. This kind of ordering is quite typical when using, for example, preference ordering based on values but not row or chronological order. In my case I wanted to sort some rows in specific order, which also was editable. The lowest value was set to zero by default telling that the row was the first in the order. The next rows in the series were numbered with a number one higher each. Now, I wanted to reorder the rows by moving a row in the middle to the beginning of the order or in other words to the first position while maintaining the order of the rest of the rows. I could of course had reserved space between these numbers by increasing each value by ten or hundred, but this method will fail as well when the gaps become all filled with new values.
The solution is to renumber the values so that there will be more space between each value again. Renumbering a column value in MySQL is very easy with only a couple of SQL lines. The following code snippet does the trick.
set @counter = 0;
update [table name]
set [column name] = (@counter := @counter + 100);The basic idea of this code is to use a counter variable. I set up the variable @counter on the first line and set the initial value. Setting the initial value to 100 starts the numbering from that value, initial value 1000 starts from 1000 respectively. The next line updates the column with the counter value. Before assigning the value the counter is first increased by 100 inside the parenthesis. The incremental value could be any reasonable integer number greater than zero. The increased value is then set as the new value of the counter variable. Finally, the counter value is set as the new value of the column. All this is accomplished on a single line and requires no further programming. Renumbering a column value is indeed extremely easy and efficient in MySQL.