sql - Numbering duplicates record in mysql (2) -

i have table , (sequence_no.) field null :

id        name      age     sequence_no. --        -----     ---     ------------ 1         sara      20          2         sara      20            3         sara      20          4         john      24          5         john      24          6         hama      23          

i want update this:

id        name      age     sequence_no. --        -----     ---     ------------ 1         sara      20         1 2         sara      20         2 3         sara      20         3 4         john      24         1 5         john      24         2 6         hama      23         1 

which query can in mysql?

thank

you can emulate row_number() using correlated subquery in mysql. resulting table sequential number join table , update value of sequence_no using generated numbers.

update  tablename         inner join         (             select  a.id,                      (                         select  count(*)                            tablename c                           c.name = a.name ,                                 c.id <= a.id) sequence_no                tablename         ) b on a.id = b.id set     a.sequence_no = b.sequence_no 

• sqlfiddle demo