back to notes

change rank order of a mysql table

Using a different table

OLD_TABLE - id - title - rank

-> Duplicate the table and add new field

NEWTABLE - id - title - rank - newrank

-> Execute query :

UPDATE NEWTABLE SET newrank = (SELECT COUNT(*)+1 FROM OLD_TABLE WHERE OLD_TABLE.rank > NEW_TABLE.rank);

Using variables

SET @ordering_inc = 1;

SET @new_ordering = 0;

UPDATE imagescopy SET expositionrank = (@newordering := @newordering + @orderinginc) WHERE expositionid = 35 ORDER BY exposition_rank ASC