mysql cursor update position index with loop count

Question

Truck Details Tables

id  Order_ref_id    tryck_type_ref_id   position_index
1   226                 24                  1
2   226                 24                  2
3   226                 32                  1
4   226                 35                  1
5   226                 35                  2
6   227                 15                  1
7   227                 15                  2
8   228                 10                  1
9   229                 32                  1
10  229                 32                  2

mysql update position index value as i shown in the table . Each order will have multiple truck types. if one truck is repeted for order for 2 times then position index will be 1 , 2. So can any one help me on this... I was tried using Cursor.. not position index is not updating correctly

Answer 1

This is easy enough to do without a cursor. You just need variables:

set @rn := 0;
set @ot := ''
update t
    set position_index = (case when @ot = concat_ws('-', Order_ref_id, tryck_type_ref_id)
                               then (@rn := @rn + 1)
                               when @ot := concat_ws('-', Order_ref_id, tryck_type_ref_id)
                               then @rn := 1
                          end)
    order by id;