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 |