Cornwell Cornwell - 20 days ago 6
MySQL Question

SQL: How to change a row order position

Let's say I have this table:

ID Name position
11 Kate 1
12 Frank 2
13 Anna 3
14 Peter 4
15 James 5
16 Michael 6


Giving the current ID and target position, I need to come up with an efficient way of reordering it.

I thought of mixing a bit of SQL with my server-side language, eg. (moving up Frank):

if (newPosition > oldPosition) {
UPDATE people SET position = position - 1 WHERE listId = 1 AND position <= @newPosition AND Name != "Frank";
UPDATE people SET position = @newPos WHERE listId = 1 AND Name="Frank";
} else {

}


One of the problems with this is that if the current position is 0, it will go negative.

Any ideas on how to reorder the rows?

EDIT: As an example of what I'm trying to do, let's say I want to move Frank (position = 2) down to between Peter (position = 4) and James (position = 6), so ideally, the table should look like this:

ID Name position
11 Kate 1
13 Anna 2
14 Peter 3
12 Frank 4
15 James 5
16 Michael 6

Answer

I'm not entirely clear how you intend for the reordering operation to work. Is this what you had in mind?

update T
set position =
    case    
        when newPosition > oldPosition then
            case when position = least(oldPosition, newPosition)
                then newPosition else position - 1 end
        else /* newPosition < oldPosition then */
            case when position = greatest(oldPosition, newPosition)
                then newPosition else position + 1 end
    end    
where position between
           least(oldPosition, newPosition)
    and greatest(oldPosition, newPosition)
    and oldPosition <> newPosition
Comments