how to automatic increase specific column value in mysql -

table 1 id | uname | flag 1  |  abc  |  0 2  |  abc  |  0 3  |  abc  |  0 4  |  bcd  |  0 5  |  bcd  |  0 6  |  cdf  |  0 7  |  ghi  |  0 8  |  ghi  |  0 

i want increase flag value according uname occurrence, starting 0

table 1 id | uname | flag 1  |  abc  |  2 2  |  abc  |  1 3  |  abc  |  0 4  |  bcd  |  1 5  |  bcd  |  0 6  |  cdf  |  0 7  |  ghi  |  1 8  |  ghi  |  0 

i want update table

you can modify table using update variables:

set @u := ''; set @rn := 0;  update table1     set flag = if(@u = uname, @rn := @rn + 1,                   if(@u := uname, 1, 1)                  )     order uname, id desc; 

unfortunately, mysql not let set variables in update when use order by. if want 1 statement, can use join:

update table1 t1 join        (select t1.*,                (@rn := if(@u = uname, @rn := @rn + 1,                           if(@u := uname, 1, 1)                          )                ) rn         table1 t1 cross join              (select @rn := 0, @u := '') params         order uname, id desc        ) tt        on t1.id = tt.id     set t1.flag = tt.rn;