So i have a table with a column 'order'. That column is linked to the columns documents and line id. I want 'order' to be in a correct sequence. Right now, for example, line 1 has 5 documents which have the order 1, 2, 4, 10, 17. Is there a way with which i can change the numbers that are out of sequence, so 4, 10, 17, to 3, 4, 5?
Is it possible to force numbers in a column to be sequential and remove possible blank spaces?
Viewed 14
1 Answers
This is a good application of MySQL's user-defined variables.
SET @i = 0;
UPDATE MyTable
SET `order` = (@i := @i+1)
ORDER BY `order`;
The expression increments @i by 1 as it processes each row in the prescribed order.
Related