根据MySQL中另一列分组的另一列的顺序更新列

若id列设置为自动递增,那个么可以在同一个表上使用带有join子句的update查询

update table1 a
join (
  select a.id,a.common_id,count(*) pos
  from table1 a
  left join table1 b on a.common_id = b.common_id
  and a.id >= b.id
  group by a.id, a.common_id
) b using(id,common_id)
set a.position = b.pos

Demo

如果只是出于选择目的,您可以将其用作

select a.id,a.common_id,count(*) pos
from table1 a
left join table1 b on a.common_id = b.common_id
and a.id >= b.id
group by a.id, a.common_id

Demo

评论后编辑 具有最小位置的应具有位置1

在您的评论之后,您可以根据职位标准进行更新,但这完全取决于common\u id、position是否唯一意味着每个common\u id应该有唯一的职位

Select

select a.common_id,a.position,count(*) pos
from table1 a
left join table1 b on a.common_id = b.common_id
and a.position >= b.position
group by a.common_id,a.position
order by a.common_id

Update

update table1 a
join (
  select a.common_id,a.position,count(*) pos
  from table1 a
  left join table1 b on a.common_id = b.common_id
  and a.position >= b.position
  group by a.common_id,a.position
) b using(common_id,position)
set a.position = b.pos