Updating a table to increment a value per user id

Question

I have the following table (called node_items):

id  node_id position
==  ======= ========
1      1       1
2      1       1
3      2       1
4      2       1
5      2       1
6      2       1
7      3       1
8      3       1
9      3       1
10     3       1

The position field is supposed to mark an items position in the node. They are currently all set at 1. I am wanting to fix this table by setting an incremental position for each item per node_id. So, the result I am after is:

id  node_id position
==  ======= ========
1      1       1
2      1       2
3      2       1
4      2       2
5      2       3
6      2       4
7      2       5
8      3       1
9      3       2
10     3       3

So I somehow need to group each item by node_id and set the position to an initial value of 1 and increment it for each subsequent item with that node_id.

Note: To keep my explanation simple, the above node_ids are shown in order (1,1, 2, 2,2,2,2,3,3,3), but this isn't usually the case.

What query can I use to update the position values incrementally for each node_id?

Answer 1

Fixing the table once can be done with an UPDATE:

SET @n = 0, @p = 1;

UPDATE node_items
SET position = (@p := IF(node_id=@n, @p+1, 1)),
    node_id = (@n := node_id)
ORDER BY node_id, id;

Making the table maintain the position values as you insert/update/delete data is harder. Basically, can't do it while allowing concurrent updates. You have to lock the table in every session that needs to do writes to the table. This makes concurrent sessions run serially.

You can read my old post about this here: Some sort of “different auto-increment indexes” per a primary key values