mysql: while loop shuold stop at 350, but it stops at 109

Question

in my MySQL workbench I have to create a wine cellar DB, i created the tables and now i have to insert values in them, i'm trying to fill a table with 350 random values, but it stops at 109 values.

What I want to do is to set vino.codice with random values from Bottiglia which has as PK codice. In Bottiglia I have different types of bottle: wine, sparkling wine and spirits. In vino.codice I only want the codes that matches to bottiglia.type = 'vino'.

this is one of the things that I tried:

drop procedure if exists in_vino;
delimiter $$
create procedure in_vino()
begin
declare c int;
declare var int;
set c = 1;
set var = (select count(codice)
            from bottiglia
            where bottiglia.tipo_bottiglia = 'vino');
start transaction;
while c <= var do
    update vino
        set codice = (select codice
                      from bottiglia
                      where bottiglia.tipo_bottiglia = 'vino'
                      order by rand()
                      limit 1);
    while c <= 175 do
            insert into vino(codice, tipo_vino, vitigno)
            select 
                codice,
                'rosso' as tipo_vino,
                elt(floor(1 + rand() * 10), 'barbera', 'dolcetto', 'nebbiolo', 'corvina', 'rondinella', 'sangiovese', 'nero d avola', 
                                 'syrah', 'cerasuolo di vittoria', 'nerello mascalese') as vitigno;
    end while;
    while c >175 and c <= 350 do
        insert into vino(codice, tipo_vino, vitigno)
        select 
            codice,
            'bianco' as tipo_vino,
            elt(floor(1 + rand() * 9), 'timorasso', 'pinot bianco', 'pinot grigio', 'soavignon blanc', 'friuliano', 'vermentino', 'pecorino', 
                         'malvasia del lazio', 'grillo') as vitigno;
    end while;
    set c = c + 1;
end while;
end $$
delimiter ;
select * from vino;

i checked the var variable and its value is 350. This is my output: 109 row(s) returned

In here you have the fiddle with table definitions and procedures to insert values that i tried: https://dbfiddle.uk/YAxr_4PR

Answer 1

I can't tell you why your code only inserts 109 rows unless you provide sample data from bottiglia preferably in a fiddle together with table definitions.

Your code looks incorrect in a number of places and should time out because the inner loops never terminate and should fail with an unknown column name codice in the insert..select statements. Additionally I don't get why you attempt to update vino when it looks like setting a variable would be appropriate. You should also commit the transaction before quitting the procedure.

Below may be(or close) to what you need

create table vino(id int auto_increment primary key,codice int, tipo_vino varchar(10), vitigno varchar(30));
create table bottiglia(codice int);



drop procedure if exists p;
delimiter $$
create procedure p()
begin
declare c int;
declare var int;
declare vcodice int;
set c = 1;
            
while c<=350 do
    insert into bottiglia values (c);
    set c=c+1;
end while;
set var := (select count(codice)
            from bottiglia
            #where bottiglia.tipo_bottiglia = 'vino'
            );
set c = 1;
start transaction;
select c,var;
while c <= var do
    #update vino
   set vcodice = (select codice
                      from bottiglia
                      #where bottiglia.tipo_bottiglia = 'vino'
                      order by rand()
                      limit 1);
    if c <= 175 then
            insert into vino(codice, tipo_vino, vitigno)
            select 
                vcodice,
                'rosso' as tipo_vino,
                elt(floor(1 + rand() * 10), 'barbera', 'dolcetto', 'nebbiolo', 'corvina', 'rondinella', 'sangiovese', 'nero d avola', 
                                 'syrah', 'cerasuolo di vittoria', 'nerello mascalese') as vitigno;
    end if;
    if c >175 and c <= 350 then
        insert into vino(codice, tipo_vino, vitigno)
        select 
            vcodice,
            'bianco' as tipo_vino,
            elt(floor(1 + rand() * 9), 'timorasso', 'pinot bianco', 'pinot grigio', 'soavignon blanc', 'friuliano', 'vermentino', 'pecorino', 
                         'malvasia del lazio', 'grillo') as vitigno;
    end if;
    set c = c + 1;
end while;
select c;
commit;
end $$
delimiter ;
truncate table bottiglia;
call p();

https://dbfiddle.uk/IQa5WGGj

NB:-the fiddle does not require delimiters to be set..