one statement mysql auto update item order with sorting-CodePudding

I have table items I want with one statement to update the table with auto increment and keep same sorting

table items

id,item_order
50,2
51,3
52,6
53,5

to be

id,item_order
50,1
51,2
53,3
52,4

CodePudding user response：

You can use a temporary variable to do it via mysql-update-a-field-with-an-incrementing-variable

CREATE TABLE test(
id int primary key,
item_order int
);

insert into test(id,item_order)
values
(50,2),
(51,3),
(52,6),
(53,5);

set @order = 0;
update test set item_order = @order:=@order 1;

select * from test;

DB Fiddle