Update a column in a mySQL data base by 1-CodePudding

I have a SQL database with 250 rows. I inserted a column filled with 0's. I updated the first rows 11 rows to be 1,2,3,4,5,6,7,8,9,10 & 11. The rest of the rows are still 0's.

How would I go about updating rows 11-250 to go from 0 to each of the relevant row value.

thank you!

CodePudding user response：

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.

CodePudding user response：

If a classic version (prior to 8.0) is being used, which does not support CTE and window functions, we have to find a workaround. To resolve the issue, we can use the user variable trick. Below are the complete steps to demonstrate it.

drop table if exists test;
create table test(id int primary key auto_increment, num int default 0);
delimiter //
drop procedure if exists makerows//
-- let's create a procedure to generate necessary rows.
create procedure makerows()
begin
declare c int default 1;

lp:loop
if c>250 then
leave lp;
end if;

if c<=11 then
insert test values(default,c);
else insert test (id) values(default);
end if;
set c=c 1;
end loop lp;

end//
delimiter ;
call makerows; -- now we have the test table with the id from 1 to 250 and the num from 1 to 11 with the rest being 0

-- here is the user variable trick to generate row id as required. Note we set the initial @row_id to -1 so we can calculate the first @row_id to be 0 for the first row (id=11).
select id ,@row_id:=@row_id 1 as row_id 
from test,(select @row_id:=-1) t 
where id>=11 order by id ;

-- the query above is used to get a derived table which shall be joined to the base table, so we can update the required num column 
update test t1 
join
(select id ,@row_id:=@row_id 1 as row_id 
from test,(select @row_id:=-1) t 
where id>=11 order by id) t2 
on t1.id=t2.id
set t1.num=t2.row_id
;

-- now we have the test table in which the id from 11 to 250 have the num column values from 0 to 239
select * from test;