MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test ( id INT AUTO_INCREMENT PRIMARY KEY, pos INT);
INSERT INTO test ( pos )
WITH RECURSIVE
cte AS ( SELECT 1 n UNION ALL SELECT n + 1 FROM cte WHERE n < 10 )
SELECT n FROM cte ORDER BY n;
SELECT * FROM test;


id
pos


1
1

2
2

3
3

4
4

5
5

6
6

7
7

8
8

9
9

10
10


CREATE PROCEDURE move_rows ( IN position_move_from INT,
                             IN position_move_to INT )
UPDATE test 
SET pos = CASE WHEN id = position_move_to
               THEN position_move_from
               ELSE pos + SIGN(position_move_to - position_move_from)
               END
WHERE id BETWEEN LEAST(position_move_from, position_move_to)
             AND GREATEST(position_move_from, position_move_to)
ORDER BY id = position_move_to;
CALL move_rows (5, 3);
CALL move_rows (7, 9);
SELECT * FROM test;


id
pos


1
1

2
2

3
5

4
3

5
4

6
6

7
8

8
9

9
7

10
10