user2848775 user2848775 - 29 days ago 23
MySQL Question

mysql cursor update position index with loop count

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

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;