Posted about 7 years ago. Visible to the public.

How to update a MySQL column with ascending numbers

Given the problem you have a new column postion and that column should be updated for all existing rows with ascending numbers. Furthermore these numbers should be generated by a special order. In order to achieve that you could do the following:

Copy
execute "SET @pos := 0;" update " UPDATE pages SET position = ( SELECT @pos := @pos + 1 ) ORDER BY updated_at DESC;"

Once an application no longer requires constant development, it needs periodic maintenance for stable and secure operation. makandra offers monthly maintenance contracts that let you focus on your business while we make sure the lights stay on.

Owner of this card:

Avatar
Ulrich Berkmueller
Last edit:
over 6 years ago
Keywords:
increase, counter, increment, migration
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Ulrich Berkmueller to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more