MySQL Case When won't work

1
votes

I'm trying to update the positions of items from a list upon updating a single item's position, e.g:

  1. item1
  2. item2
  3. item3
  4. item4
  5. item5

Moving item5 to position 2 would update the list to:

  1. item1
  2. item5 << new position
  3. item2 << [moved down one]
  4. item3 << [moved down one]
  5. item4 << [moved down one]

.

UPDATE subjects SET  
position = (CASE WHEN position < {$position} THEN position + 1
WHEN position > {$position} THEN position - 1 else position END CASE)
WHERE position <= position AND position >= {$position};

This SQL query does not work. However what I'd also like to do is IF position < {$position} then have the WHERE clause as what it is now, but if position > {$position} I'd like to have the <= and >= swap around (this will subsequently allow for updating when moving positions up OR down the list

2
mysqlsqllistif-statement
What's the error that you get? - Cfreak
I think it does execute, however, he's not satisfied with the response. At least, that's how I read it. - Digitalis
Database query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE) WHERE position <= position AND position >= 1 at line 3 - Justin
CASE statements just have END, not END CASE. Remove that last CASE - Michael Berkowski
Okay that's fixed the SQL, thank you. Now how would I do the latter, does anyone know? - Justin

2 Answers

2
votes

I am not sure what the requirement of WHERE position <= position is, since position will always equal position in the same row...

I also can't see how your update statement would work with just one parameter, surely you need the current position of the item to update as well as the position to move it to?

Assuming the above is right you could write your query as follows (using 2 parameters $new for the new position and $current for the current position):

UPDATE  subjects
SET     position = CASE WHEN position = {$current} THEN {$new} 
                        ELSE position + SIGN({$current} - {$new}) 
                    END
WHERE   position BETWEEN LEAST({$current}, {$new}) AND GREATEST({$current}, {$new})

The premise is that only rows between the current value and the new value need to be updated which is covered by the where clause. Next the update statement uses the SIGN function to establish if the move is a shift up or shift down, adding or removing 1 from the current position (Except for the row to be moved which is updated with the new position)

WORKING EXAMPLE

http://sqlfiddle.com/#!2/99c64/1

I've had to do the update in the schema section so you can't see exactly how the table has changed in the SQL Fiddle, however you should see from the results that the row with value "3" has moved to position 8 from position 3, and everything from 8 to 4 has had it's position shifted down by one. In the following fiddle the process is reversed (row 8 is moved to 3 and 3 to 7 shifted up 1).

http://sqlfiddle.com/#!2/a087b/1

0
votes

If position has a UNIQUE constraint, you need 2 or 3 steps due to the non-standard way that MySQL's UPDATE statement works:

UPDATE subjects 
  SET position = -67             --- an impossible value
WHERE position = 5 ;

UPDATE subjects 
  SET position = position + 1     
WHERE position BETWEEN 2 AND 5 
ORDER BY position DESC ;   

UPDATE subjects 
  SET position = 2
WHERE position = -67 ;