MYSQL - Updating a column with incrementing value that will reset everytime a condition is met

Question

I have this table.

++++++++++++++++++++++++++++++++
+ itemid +   name   + group_id +
++++++++++++++++++++++++++++++++
+   1    +  name 1  +    0     +
+   2    +  name 2  +    1     +
+   3    +  name 3  +    1     +
+   4    +  name 4  +    0     +
+   5    +  name 5  +    0     +
+   6    +  name 6  +    2     +
+   7    +  name 7  +    2     +
+   8    +  name 8  +    2     +
+   9    +  name 9  +    3     +
+   10   +  name 10 +    3     +
+   11   +  name 11 +    3     +
+   12   +  name 12 +    3     +
+   13   +  name 13 +    0     +
+   14   +  name 14 +    4     +
+   15   +  name 15 +    0     +
++++++++++++++++++++++++++++++++

I added a new column group_order_id with default value 0 to this table so that there will be an order between items within a group.

+++++++++++++++++++++++++++++++++++++++++++++++++
+ itemid +   name   + group_id + group_order_id +
+++++++++++++++++++++++++++++++++++++++++++++++++
+   1    +  name 1  +    0     +       0        +
+   2    +  name 2  +    1     +       0        +
+   3    +  name 3  +    1     +       0        +
+   4    +  name 4  +    0     +       0        +
+   5    +  name 5  +    0     +       0        +
+   6    +  name 6  +    2     +       0        +
+   7    +  name 7  +    2     +       0        +
+   8    +  name 8  +    2     +       0        +
+   9    +  name 9  +    3     +       0        +
+   10   +  name 10 +    3     +       0        +
+   11   +  name 11 +    3     +       0        +
+   12   +  name 12 +    3     +       0        +
+   13   +  name 13 +    0     +       0        +
+   14   +  name 14 +    4     +       0        +
+   15   +  name 15 +    0     +       0        +
+++++++++++++++++++++++++++++++++++++++++++++++++

I want to update the column group_order_id such that:

• if group_id=0, group_order_id=0
• if group_id is the same (except 0), the value of group_order_id of each item will increment from 1-X like below.

+++++++++++++++++++++++++++++++++++++++++++++++++
+ itemid +   name   + group_id + group_order_id +
+++++++++++++++++++++++++++++++++++++++++++++++++
+   1    +  name 1  +    0     +       0        +
+   2    +  name 2  +    1     +       1        +
+   3    +  name 3  +    1     +       2        +
+   4    +  name 4  +    0     +       0        +
+   5    +  name 5  +    0     +       0        +
+   6    +  name 6  +    2     +       1        +
+   7    +  name 7  +    2     +       2        +
+   8    +  name 8  +    2     +       3        +
+   9    +  name 9  +    3     +       1        +
+   10   +  name 10 +    3     +       2        +
+   11   +  name 11 +    3     +       3        +
+   12   +  name 12 +    3     +       4        +
+   13   +  name 13 +    0     +       0        +
+   14   +  name 14 +    4     +       1        +
+   15   +  name 15 +    0     +       0        +
+++++++++++++++++++++++++++++++++++++++++++++++++

Is there a way to accomplish this?

Answer 1

You need to use a ranking query and then use it as a subquery inside an UPDATE statement, like this:

Ranking Query:

SELECT IF(@prev = group_id, @s:=@s+1, @s:=1) AS `group_order_id`, itemid, @prev:=group_id AS group_id
FROM table1, (SELECT @s:= 1, @prev:= 0) s
WHERE group_id <> 0
ORDER BY group_id

Update query:

UPDATE table1, (
  SELECT IF(@prev = group_id, @s:=@s+1, @s:=1) AS `group_order_id`, itemid, @prev:=group_id AS group_id
  FROM table1, (SELECT @s:= 1, @prev:= 0) s
  WHERE group_id <> 0
  ORDER BY group_id
) AS t
SET table1.group_order_id = t.group_order_id
WHERE table1.itemid = t.itemid

Working Demo: http://sqlfiddle.com/#!2/08259/1/0