How to number lines?

Question

MySQL

There is a forum (flarum) I am
migrating from the old forum to flarum.
There is a field - the number of the message in the thread.
The task is to number the number column in order where disqussion_id = 1
UPD:
set @cnt=0;
UPDATE posts SET number = (@cnt:[email protected]+1) WHERE discussion_id = 1
How to do it for the entire database? I have tens of thousands of discussions? how to group. but the grouping in the update does not work

Answer 1

More or less like this:

-- селект номеров строчек в разрезе дискусий. аналог ms sql row_number over ( partition by ...

    SELECT 
           @row_number:=CASE WHEN @discussion_id=discussion_id THEN @row_number+1 ELSE 1 END AS ROW_NUMBER
           ,@discussion_id:=discussion_id AS discussion_id
           ,message_id
    FROM posts, (SELECT @row_number:=0,@discussion_id:=0) AS t
    ORDER BY discussion_id

   -- http://www.mysqltutorial.org/mysql-update-join/ 
   UPDATE posts p 
   JOIN (
       SELECT 
       @row_number:=CASE WHEN @discussion_id=discussion_id THEN @row_number+1 ELSE 1 END AS row_number
       ,@discussion_id:=discussion_id AS discussion_id
       ,message_id
       FROM posts, (SELECT @row_number:=0,@discussion_id:=0) AS t
   ) r  
   ON r.discussion_id = p.discussion_id AND r.message_id = p.message_id
   SET p.number = r.row_number

can't check