By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
id INT, -- identifier
pos INT -- position
);
-- fill test table with sample data
CREATE PROCEDURE restore_state ( amount INT )
BEGIN
TRUNCATE test;
INSERT INTO test (id, pos)
WITH RECURSIVE
cte AS (
SELECT 1 id
UNION ALL
SELECT id + 1 FROM cte WHERE id < amount
)
SELECT id, id FROM cte;
END
-- look at sample data
CALL restore_state(10);
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 |
SET @pos_from = 3; -- current position
SET @pos_to = 7; -- desired position
CALL restore_state(10); -- restore sample data
-- move the row within the rows ordering
UPDATE test
SET pos = CASE pos WHEN @pos_from THEN @pos_to
ELSE pos + SIGN(@pos_from - @pos_to)
END
WHERE pos BETWEEN LEAST(@pos_from, @pos_to) AND GREATEST(@pos_from, @pos_to);
-- check final data state
SELECT * FROM test;
| id | pos |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 7 |
| 4 | 3 |
| 5 | 4 |
| 6 | 5 |
| 7 | 6 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
SET @pos_from = 7;
SET @pos_to = 3;
CALL restore_state(10);
UPDATE test
SET pos = CASE pos WHEN @pos_from THEN @pos_to
ELSE pos + SIGN(@pos_from - @pos_to)
END
WHERE pos BETWEEN LEAST(@pos_from, @pos_to) AND GREATEST(@pos_from, @pos_to);
SELECT * FROM test;
| id | pos |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
| 6 | 7 |
| 7 | 3 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |