SQL: how to change the row order position

Question

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

By providing the current id and target position, I need to find an effective way to reorder.

I thought of mixing a little SQL with my server language, for example. (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 is that if the current position is 0, it will be negative.

Any ideas on how to reorder the lines?

EDIT: As an example of what I'm trying to do, let's say I want to move Frank (position = 2) down 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 1

, . , ?

update T
set position =
    case    
        when newPosition > oldPosition then
            case when position = least(oldPosition, newPosition)
                then newPosition else position - 1 end
        else /* newPosition < oldPosition */
            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