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 must be updated alphabetically from the other title field. Sorting by field title is excluded, because the table is a hierarchical menu where the user can change the order by setting order_id. Using server-side processing (php) separately is also unprofitable - an unnecessary separation of business logic if it can be done in a database, and in case of large amounts of data it takes more resources.

Decision

MySQL unfortunately lacks this feature: ROWNUM, RANK (),
ROW_NUMBER () as in ORACLE or MSSQL. The presence of _rowid does not affect
on the situation. In our case, you can use variables entered from the fifth version (or earlier):
SET @rank=0;
SELECT @rank:=@rank+1 AS rank, id FROM menu;
To update such a table through myself, I did not succeed, so I simply copied the table under a different name and executed the following query:
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;


Original