Update a sequential int order field based on moving something with an ID to a specific position.

update_order.sql

SET @id = 3;
SET @to = 9;


SET @lower = IF(@from < @to, @from, @to);
SET @upper = IF(@from < @to, @to, @from);

UPDATE `ordering`
SET `order` = 
  IF(`id` = @id, 
    @to, 
    IF(`order` <= @upper AND `order` >= @lower, 
      IF(@from < @to, 
        `order` - 1, 
        `order` + 1
      ), 
      `order`
    )
  );