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.


    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;

    Also popular now: