-- селект номеров строчек в разрезе дискусий. аналог 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