Update a column in a mySQL data base by 1

There are two approach (which i can think of) to this problem. You can either using window function alternatively, you can use variable.

Assuming you have the following test table:

create table salsa (id int, name varchar(255), new_column int);

insert into salsa values
    (1, 'a', 0),
    (2, 'b', 0),
    (4, 'c', 0),
    (3, 'e', 0),
    (9, 'f', 0);

Using Variable

The variable one is pretty straight forward. Though you have less control over which one first. This part is adapted from row_number() in mysql.

with
    my_ranking as (
      select s.*,
            @rownum := @rownum + 1 as _rank
     from salsa s,
          (select @rownum := 0) r
   )
update salsa as s1
   set new_column = (
     select _rank
     from my_ranking as s2
     where s1.id = s2.id
     limit 1
   );

The @rownum variable is used to keep track the current rank of the row.

See fiddle: https://www.db-fiddle.com/f/Jmn5x34WXaBJq7oyemtXp/0

Using Window Function

The window functions comes with rank() which we will use this time. It require at least mysql version 8 to use.

with
   my_ranking as (
     select *,
            rank() over(order by id) as _rank
     from salsa
   )
update salsa as s1
   set new_column = (
     select _rank
     from my_ranking as s2
     where s1.id = s2.id
     limit 1
   );

The reason i’m asking for point of reference how to rank the rows is that, the rank() need to be partitioned to work otherwise everyone is rank 1. The important part is the:

rank() over(order by id) as _rank

To determine the "position" of the row, you could use id or other column as you needed.

See fiddle: https://www.db-fiddle.com/f/nwLv9R7weQt4e5RhUbgaUL/0

Note:

• Both query above need mysql 8 given I used CTE (Common Table Expression) because I’m too lazy to write them as subqueries.
• There might be better query out there, think of this as duct tape or something.