Execute SQL UPDATE with user defined variable on each result of a select

Question

This is a migration script that will be run once, so performance is not a issue.

I have a table sb_nav_page and a table sb_module. A module is belonging to one page (foreign key id_page_module);

CREATE TABLE IF NOT EXISTS `sb_nav_page` (
   `id_page` int(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_page`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `sb_module` (
  `id_module` int(11) NOT NULL AUTO_INCREMENT,
  `id_page_module` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_module`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

I need to add and populate a new column zindex on sb_module that will represents the initial order of the modules within a page based on previous ordering rules.

mysql> SELECT id_module, name_module, zindex_module from sb_module  
    WHERE id_page_module = 148 ;
+-----------+-------------+---------------+
| id_module | name_module | zindex_module |
+-----------+-------------+---------------+
|       560 | A           |            0  |
|       561 | A           |            0  |
|       562 | B           |            0  |
+-----------+-------------+---------------+
3 rows in set (0.00 sec)

The following SQL UPDATE does the correct initialization for one page (id_page = 148).

mysql> SET @x = 0;
mysql> UPDATE sb_module SET zindex_module = (@x:=@x+1) WHERE 
    id_page_module = 148     
    ORDER BY name_module DESC, id_module ASC;

mysql> SELECT id_module, name_module, zindex_module from sb_module  
    WHERE id_page_module = 148 ;
+-----------+-------------+---------------+
| id_module | name_module | zindex_module |
+-----------+-------------+---------------+
|       560 | A           |            2  |
|       561 | A           |            3  |
|       562 | B           |            1  |
+-----------+-------------+---------------+
3 rows in set (0.00 sec)

But I can figure out how to do it for all the pages, i.e. for all SELECT id_page from sb_nav_page.

Answer 1

You can do this using variables:

SET @x = 0, @p = -1;
UPDATE sb_module
    SET zindex_module = (@x := if(@p = id_page_module, @x + 1,
                                  if(@p := id_page_module, 1, 1)
                                 )
                        )
ORDER BY id_page_module, name_module DESC, id_module ASC;

• The first if checks if we are on the same page as the last module.
• The second if set the new id_page_module and reset @x.

An index on sb_module(id_page_module, name_module, id_module) would help performance.