Mysql rownum

Formulation of the problem

There is a menu table in the database with the order_id order field , but the order is incorrect and the order needs to be updated alphabetically of another title field. Sorting by the title field is excluded because the table is a hierarchical menu where the user can change the order by setting order_id. Using server processing (php) separately is also unprofitable - unnecessary separation of business logic if this can be done in the database, and even in case of large amounts of data it will take more resources.

Decision

In MySQL, unfortunately there is no such possibility of ROWNUM, RANK (),
ROW_NUMBER () as in ORACLE or MSSQL. The presence of _rowid has no effect
on the situation. In our case, you can use the variables entered from the fifth version (or earlier): To update such a table through myself, I did not succeed, so I just copied the table under a different name and performed such a request: Original
SET @rank=0;
SELECT @rank:=@rank+1 AS rank, id FROM menu;

SET @rank=0;
UPDATE `menu` SET order_id=(
SELECT @rank:=@rank+1 FROM `menu2` WHERE `menu`.id=`menu2`.id LIMIT 1
) WHERE parentID=0 ORDER BY `title` ASC;