php - update from select incremental mysql -

i've been trying find solution , found several similar posts none of them answer question.

i have badly designed table stores sorting index. programatically update index people sort data. problem indexes 1, 2, 2, 3, 6, 6, 7, 8, 8, etc i'm tasked fixing problem. fixed code doesnt happen anymore how update db? have table:

+---------+-------+---------+ | otherid | index | product | | 3423    | 1     | zbhdfji | | 63453   | 3     | fgdfgr  | | 75454   | 3     | drhfef  | +---------+-------+---------+ 

i need like:

+---------+-------+---------+ | otherid | index | product | | 3423    | 1     | zbhdfji | | 63453   | 2     | fgdfgr  | | 75454   | 3     | drhfef  | +---------+-------+---------+ 

i tried like:

update table set index = @rownum otherid in (select other_id table index <= 200 order index asc ); 

but not liking i'm pulling same table , i'm not sure if work. ideas?

set @row_num := 0; update `table` set index = @row_num := @row_num + 1 order otherid;