mysql将序号更新为到字段值priority出现了意想不到

Question

假如有表 student 有id为 1-10的10条记录，此次更新表student 中字段 priority 应为1-10，升序

update  student as  r INNER  JOIN  
(
  select (@i:=@i+1 ) as row_num,  a.id, a.priority  from student as a , (select @i:=0) d  where a.priority >=0   ORDER BY a.priority asc 
) as  t1
 on r.id =t1.id 

set r.priority = t1.row_num

此时执行此条语句 当前表中记录为priority 字段 1，3-12升序

update  student as  r INNER  JOIN  
(
  select (@i:=@i+1 ) as row_num,  a.id, a.priority  from student as a , (select @i:=2) d  where a.priority >=2   ORDER BY a.priority asc 
) as  t1
 on r.id =t1.id 

set r.priority = t1.row_num

当我设置id为10 priority 为12 的记录的priority 字段的值为2 后再次执行

update  student as  r INNER  JOIN  
(
  select (@i:=@i+1 ) as row_num,  a.id, a.priority  from student as a , (select @i:=2) d  where a.priority >=2   ORDER BY a.priority asc 
) as  t1
 on r.id =t1.id 

set r.priority = t1.row_num

执行后
id为10的这条记录priority 应该变成3 但是结果却变成12

问题1：这是什么原因导致的？
问题2：这个怎么优化。

Answer 1

原因找到了，更新时关联的sql子查询orderby会失效，解决方法是将关联sql设为子查询，外面包裹设置orderby相同的参数即可，