Обновите список вещей, не поражая каждую запись

Вы можете сделать это с помощью одного оператора Update, например, так:

Update Table
Set OrderValue = Case
                    When Table.ItemName = 'apples' Then 0
                    Else    (
                            Select Count(*)
                            From Table As T1
                            Where T1.ItemName <> 'apples'
                                And T1.OrderValue < Table.OrderValue
                            ) + 1
                    End + 1

Очевидно, вы замените яблок выбранным значением. Однако я думаю, что этот тип сортировки лучше всего выполнять в клиентском приложении, а не в базе данных.

Если бы вы использовали SQL Server, вы могли бы сделать это, используя представление связного списка и CTE. Я не знаю, поддерживает ли mysql CTE ...

SET NOCOUNT ON
GO

DROP TABLE [Item]
GO

CREATE TABLE [Item]
(
    [ItemId] int NOT NULL PRIMARY KEY,
    [Name] varchar(100) NOT NULL,
    [PreviousId] int NULL
)
GO

INSERT [Item] VALUES (6, 'apples', 3)
INSERT [Item] VALUES (3, 'orange', 36)
INSERT [Item] VALUES (9, 'mango', 100)
INSERT [Item] VALUES (100, 'salad', NULL)
INSERT [Item] VALUES (36, 'banana', 9)
GO

;WITH
[LinkedItem] AS
(
    SELECT
        [Item].*,
        1 AS [OrderValue]
    FROM [Item]
    WHERE [Item].[PreviousId] IS NULL
    UNION ALL
    SELECT
        [Item].*,
        [LinkedItem].[OrderValue] + 1
    FROM [Item]
        INNER JOIN [LinkedItem] ON [LinkedItem].[ItemId] = [Item].[PreviousId]
)
SELECT *
FROM [LinkedItem]
ORDER BY
    [LinkedItem].[OrderValue]

-- Drag orange up two spaces
DECLARE @MovingItemId int
DECLARE @NewPreviousId int
SET @MovingItemId = 3
SET @NewPreviousId = 100

DECLARE @OldPreviousId int
SELECT @OldPreviousId = [PreviousId] FROM [Item] WHERE [ItemId] = @MovingItemId
UPDATE [Item] SET [PreviousId] = @OldPreviousId WHERE [PreviousId] = @MovingItemId
UPDATE [Item] SET [PreviousId] = @MovingItemId WHERE [PreviousId] = @NewPreviousId
UPDATE [Item] SET [PreviousId] = @NewPreviousId WHERE [ItemId] = @MovingItemId

Это дает следующие результаты до и после:

100 salad  NULL    1
9   mango  100 2
36  banana  9   3
3   orange  36  4
6   apples  3   5

100 salad   NULL    1
3   orange  100 2
9   mango   3   3
36  banana  9   4
6   apples  36  5

1. Как было предложено ранее, и если вам не нужно показывать всем пользователям текущий порядок, на который влияет данный пользователь, я бы предложил сначала обработать это в клиенте (есть много способов решения этой проблемы), а затем, основываясь на действии пользователя (нажатие кнопки "Я закончил", например), обновить строки в базе данных окончательным порядком из структуры, которую вы выбрали для хранения в клиенте.

2. Вы можете сделать код в клиенте настолько сложным, насколько хотите, чтобы попытаться минимизировать количество строк, которые необходимо обновить в базе данных: в некоторых случаях вам может понадобиться вставить только одну строку (если пользователь вставляет новый элемент в конец списка); во многих случаях вам может понадобиться обновить две строки (если пользователь просто меняет местами два последовательных элемента). Наихудший сценарий с точки зрения количества строк, которые необходимо обновить, - это все строки (вы можете создать алгоритм, который будет определять только те строки, которые необходимо обновить, и обновлять только их). Выбор за вами - стоит ли делать это или просто выпустить обновление всех строк.

3. Суть в том, что вам не нужно обновлять все строки в базе данных, эта ситуация - лишь один из многих возможных сценариев. Некоторые базы данных позволяют выполнять обновление в массовом порядке (название может отличаться в зависимости от базы данных), и это не будет стоить очень дорого.

Я предполагаю, что у вас есть первичный ключ в вашей таблице, столбец id. Эти два утверждения должны сработать.

update table set order_value=0 where itemname='apples';
update 
(select @num := 0 )vars
straight_join 
(select id, @num := @num+1 as ord_value
from table 
order by order_value
)big
inner join table t on t.id = big.id
set t.order_value = big.ord_value;

Если у вас нет id, используйте вместо него itemname.

Я не уверен, что это можно считать решением, но буквально не нужно делать одно обновление для каждой строки. Если вы переместите 'foo' из позиции 4 в позицию 1, вы просто сделаете

UPDATE table SET position = 1 WHERE itemname = 'foo'
UPDATE table SET position = position + 1 WHERE itemname != 'foo' AND position < 4

Это то же количество обновлений, даже если вы перемещаетесь с позиции 1000 на 500 или с 500 на 1000 (хотя вам нужно перевернуть это, естественно), вам просто нужно массово сдвинуть все затронутые строки на плюс-минус один