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?

CodePudding user response：

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.