Sequentially update mysql table

Question

Given the following table, how can I sequentially reorder position from 1 to N using a single query after one or more rows have been deleted and still preserve the order of position?

+---------+----------+-----+
| id (pk) | position | fk  |
+---------+----------+-----+
|       4 |        1 | 123 |
|       2 |        2 | 123 |
|      18 |        3 | 123 |
|       5 |        4 | 123 |
|       3 |        5 | 123 |
+---------+----------+-----+

For instance, if position=1 (id=4) was deleted, the desired final records are:

+---------+----------+-----+
| id (pk) | position | fk  |
+---------+----------+-----+
|       2 |        1 | 123 |
|      18 |        2 | 123 |
|       5 |        3 | 123 |
|       3 |        4 | 123 |
+---------+----------+-----+

and if position=3 (id=18) was deleted, the desired final records are:

+---------+----------+-----+
| id (pk) | position | fk  |
+---------+----------+-----+
|       4 |        1 | 123 |
|       2 |        2 | 123 |
|       5 |        3 | 123 |
|       3 |        4 | 123 |
+---------+----------+-----+

I can do something like the following if only row was deleted but not for multiple rows.

DELETE FROM mytable WHERE fk=123 AND position = 4;
UPDATE mytable SET position=position-1 WHERE fk=123 AND position > 4;

Answer 1

User-defined variables to the rescue if you're not already using MySQL 8, which provides window functions like ROW_NUMBER():

UPDATE t
JOIN (

    SELECT 
    t.*
    , @n := @n + 1 as n
    FROM t
    , (SELECT @n := 0) var_init
    ORDER BY position

) sq ON t.id = sq.id
SET t.position = sq.n;

• see it working live in an sqlfiddle

BONUS:

It gets slightly more complicated, when you have multiple groups.
For example, for sample data like this

|  id | position |  fk |
|-----|----------|-----|
|   4 |        1 | 123 |
|   2 |        2 | 123 |
|   5 |        4 | 123 |
|   3 |        5 | 123 |
|  40 |        1 | 234 |
|  20 |        2 | 234 |
| 180 |        3 | 234 |
|  30 |        5 | 234 |

the query would be

UPDATE t
JOIN (

    SELECT 
    t.*
    , @n := if(@prev_fk != fk, 1, @n + 1) as n
    , @prev_fk := fk
    FROM t
    , (SELECT @n := 0, @prev_fk := NULL) var_init
    ORDER BY fk, position

) sq ON t.id = sq.id
SET t.position = sq.n;

Here you just save the current fk in another variable. When the next row is processed, the variable still holds the value of the "previous row". Then you reset the @n variable, when the value changes.

• see it working live in an sqlfiddle

UPDATE:

In MySQL 8 you can use the window function row_number() like this:

update t join (
    select t.*, row_number() over (partition by fk order by position) as new_pos 
    from t
) sq using (id) set t.position = sq.new_pos;