beautiful technology

Update Row Values in MySQL Based on Order Clause Sep 03 2016

Say you have a database table which contains an integer sort order column, but it’s incorrect. Perhaps you forgot to sort before creating the records. Let’s say you have another column which, when sorted, would produce the correct order. You can use that column to fix the sort order column in pure SQL. Here’s an example using the MODX CMS, which has a menuindex column (the sorting column), and a pagetitle column. The code below sets the menuindex such that the menuindex column will be in alphabetical order by the pagetitles.

        SET @rownumber = 0;    
        UPDATE modx_site_content
        SET menuindex = (@rownumber:=@rownumber+1)
        WHERE parent = 150
        ORDER BY pagetitle ASC