Move position of MySQL row up/down using php

Question

I am fetching my page's menu from a MySQL table as an unordered list <ul></ul> ORDERERED BY their position, Here is my MySQL table's screenshot

I want my query to work in a way like when I fetch record of page_id_pk=3, it SET its page_position from 3 to 2 and updates it so now it holds page_position 2. The rest of the pages (rows) move down by an increment of one. I don't want to just swap two rows but to change their position i.e their page_id_pk sequence will remain same just their position gets changed.

Answer 1

Something like this for swapping

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

And this for moving

-- 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