MySQL 8.0

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

CREATE TABLE test (entity INT, position INT);
INSERT INTO test VALUES
(1,1),
(2,2),
(3,3),
(4,4),
(5,5),
(6,6),
(7,7),
(8,8),
(9,9);
SELECT * FROM test ORDER BY position;


entity
position


1
1

2
2

3
3

4
4

5
5

6
6

7
7

8
8

9
9


SET @from := 7;
SET @to := 3;
UPDATE test
SET position := CASE WHEN position = @from 
                     THEN @to
                     ELSE position + SIGN(@from - @to)
                     END
WHERE position BETWEEN LEAST(@from, @to) AND GREATEST(@from, @to); 
SELECT * FROM test ORDER BY position;


entity
position


1
1

2
2

7
3

3
4

4
5

5
6

6
7

8
8

9
9