MySQL query for navigation CMS

Question

I'm currently trying to work out a way to make my navigation items moveable via dynamic mysql/databases.

Currently I have a table looking like this

id menuText posId int random text 1-999 number

The thing is, depending on the number in posId. The menuText will be sorted by it. What I'm trying to do is add a function to deduct, and add the number 1 to the posId table 1+1 = 2 and so on.

Hard mode, if anyone can come up with a way to make the current posId 2 into 1 when 1 becomes 2 - It'd be appreciated.

(Sorry for having trouble explaining my problem, is having a migraine.)

Answer 1

Use a CASE to flip the values:

UPDATE
  tablename
SET
  columnname = CASE 
                 WHEN columnname = 2 THEN 1
                 WHEN columnname = 1 THEN 2
               END
WHERE
  id IN (113, 847);