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!
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);
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
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:
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;
Create a user-defined variable (@rownumber for example) to increment a value of 1.
UPDATE your_table
SET your_column = (@rownumber := 1 + @rownumber)
WHERE 0 = (@rownumber := 0)
Result:
| Input | Output |
|-------|--------|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
| 11 | 11 |
| ... | ... |
| 0 | 245 |
| 0 | 246 |
| 0 | 247 |
| 0 | 248 |
| 0 | 249 |
| 0 | 250 |
Demo here.
