php - re-order a table based on sort column and another column value

Question

I have a table which has a sort_no column and the sort values are belonging to q_id which corresponds to question id. But, it does not include proper sorting values. Sometimes, the sort numbers are being repeated for different records with the same q_id. I have to refactor this table with unique sort numbers for each question.

This is a sample data I already have:

 id |   name  | sort_no | q_id
-------------------------------
 1  |  val_1  |    1    | 21
 2  |  val_2  |    2    | 21
 3  |  val_3  |    1    | 32
 4  |  val_4  |    3    | 21
 5  |  val_5  |    2    | 32
 6  |  val_6  |    2    | 32
 7  |  val_7  |    1    | 25
 8  |  val_8  |    1    | 21
 9  |  val_9  |    1    | 21
-------------------------------

This is what it should be:

 id |   name  | sort_no | q_id
------------------------------
 1  |  val_1  |    1    | 21
 2  |  val_2  |    2    | 21
 3  |  val_3  |    1    | 32
 4  |  val_4  |    3    | 21
 5  |  val_5  |    2    | 32
 6  |  val_6  |    3    | 32
 7  |  val_7  |    1    | 25
 8  |  val_8  |    4    | 21
 9  |  val_9  |    5    | 21
-------------------------------

Actually, I can fetch the records and put them in a loop and update it by a loop. But, as you know, it takes time and resource. The table is huge with millions of records.

I was wondering if I could do it directly in MySQL with a nested query.

I have no idea about the query.

Have anybody experienced this before?

Answer 1

update test5
   set sort_no=@srt:=if(@grp=q_id,@srt+1,1),
       q_id=@grp:=q_id
 where (0,0)=(select @grp:=0,@srt:=0)
 order by q_id, `name`

Set needed 'order by'. First column in 'order by' must be "q_id".

NOTE: before running this query, the update safe mode should be disabled (if not by default):

SET SQL_SAFE_UPDATES = 0;