MySQL Case When won't work

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

Answers


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


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 ;   

Need Your Help

How can I get info from 2 tables into one sql query?

mysql sql union union-all

I have these 2 tables that I need the boat and image info from to show in the same sql/loop.

How can I write an Automator action in bash with files and folders as arguments?

xcode bash automator

When I create a Automator action in XCode using Bash, all files and folder paths are printed to stdin.