上移下移mysql查询_使用php上移/下移MySQL行的位置

交换这样的东西

UPDATE `position_table` SET `page_position` = CASE

WHEN (`page_position`=1) THEN 2

WHEN (`page_position`=2) THEN 1

END

WHERE `page_position` in (1, 2) -- last line to prevent whole table scan

这是为了移动

-- old - old_position, new - new_position

UPDATE `position_table` SET

`page_position` = CASE

WHEN (`page_position` = old) THEN

new -- replace new within old

WHEN (`page_position` > old and `page_position` <= new) THEN

`page_position`- 1 -- moving up

WHEN (`page_position` < old and `page_position` >= new) THEN

`page_position`+ 1 -- moving down

ELSE

`page_position` -- otherwise lets keep same value.

END