Update value in a column based on another column in the same table in MYSQL

Question

I have a table with three columns

• vid - auto zoom column
• video_id - containing numbers
• a_id - containing unwanted numbers

The table is as follows.

Vid    Video_id    a_id
101     1            3
102     1            3
103     5            3
104     5            3
105     5            3
106     11           3
107     11           3
108     11           3
109     11           3
110     11           3         

I want to update a_id column values ​​based on video_id values. The values ​​in a_id should be updated as below.ex: If five 11 digits are specified in video_id, the value in a_id should be updated from 1 to 5.

Vid    Video_id    a_id
101     1            1
102     1            2
103     5            1
104     5            2
105     5            3
106     11           1
107     11           2
108     11           3
109     11           4
110     11           5

Answer 1

, , a_id .

update t
join (
    SELECT 
    Vid,
    @r:= CASE WHEN Video_id = @g THEN @r+1  ELSE @r:=1 END a_id 
    ,@g:=Video_id
    FROM t,(SELECT @r:=0,@g:=0) t1
    ORDER BY Video_id
) t1
on(t.Vid =t1.Vid)
set t.a_id = t1.a_id