stored procedure updates zero rows

I am creating a stored procedure that in theory should update roughly 13K items in my database. I am creating this on my own personal machine, thus the localhost and root username.

In theory it should work, I select the proper tables and columns for the data to be updated, yet when I run the procedure, instead of seeing ~13K rows being edited, it returns zero rows. I cannot remove any of the where clauses in the update statement (which I presume is what is giving me the headache), as any changes in that will make the db try to update a nulled price, as the price it's trying to update doesn't exist.

the comment with the seemingly random numbers is the product ID I am testing on. in the DB there's 156 entries of it, then each entry has 6 different colors. And then we sell the item over 14 different shops, 2 shops aren't used for now, so those do not run any updates

I am unfamiliar with cursors also, so any help with it would be appreciated.

create
    definer = root@localhost procedure Run_replace_feature_prices_weight(IN id_product_in int)
    comment 'For quick updating SQL statements which has to be done for alle shops'
BEGIN

    -- 512020060

    DECLARE id_shop_default INT;
    DECLARE id_shop_counter INT;
    DECLARE id_attribute INT;
    -- declare variables for step 1
    declare stdPrice decimal(20, 6);
    declare stdWeight decimal(20, 6);

    -- declare variables for step 2, 3, 5

    declare price_impact decimal(20, 6);
    declare factor decimal(4, 2);
    declare price_with_impact decimal(20, 6);
    declare price_with_impact_and_factor decimal(20, 6);

    /*
    unused for now, part of step 4
    declare height int default 0;
    declare length int default 0;
    declare width int default 0;
    */

    -- declare variables for cursor manipulation
    declare doneproduct bool default false;

    declare product_cursor cursor for -- cursor for looping through the individual products
        select id_product_attribute, price
        from sockelsat_product_attribute
        where id_product
                  = id_product_in;

    declare continue handler for not found set doneproduct = true;


    SET id_shop_default = 1; /*id_shop_default*/
    SET id_shop_counter = 2;


    /*STEP 1 - GET standard weight and price from sockelsat_product where id_product = id DONE
    
      STEP 2 - LOOP THRU id_feature, weight_impact, price, length, width, heigth 
         from sockelsat_product_attribute where id_shop = id_shop_default and id_product = id
             need to join sockelsat_product_attribute to sockelsat_product_attribute_shop on id_shop
      STEP 3 - GET factor from sockelsat_shop where id_shop = id_shop_counter
      STEP 4 - IS VOLUME (length * width * heigth > 0,1 then weigth_impact = 25 - weigth
      STEP 3 - UPDATE features per shop in sockelsat_product_attribute_shop with weight_impact and price * factor
      
 
      price impact = comes onto base price, dependent on size
      factor = comes onto price after price impact calculation. Dependent on country
    */
    -- getting standard weight and prices from sockelsat_product, want to assign them to local variables
    -- step 1 done
    select weight, price as standardPrice
    into stdWeight, stdPrice
    from sockelsat_product sp
    where sp.id_product = id_product_in
    limit 1;


    -- above me works


    label1:
    WHILE id_shop_counter <= 16
        DO

            -- select concat('entered while loop');

            /*
            STEP 2 - LOOP THRU id_product_attribute price (which is price impact)
              from sockelsat_product_attribute where id_shop = id_shop_default and id_product = id
                  need to join sockelsat_product_attribute to sockelsat_product_attribute_shop on id_shop
            STEP 3 - GET factor from sockelsat_shop where id_shop = id_shop_counter
            skip step 4
            STEP 5 - UPDATE features per shop in sockelsat_product_attribute_shop with weight_impact and price * factor



            price impact = comes onto base price, dependent on size (sockelsat_product_attribute price (which is price impact))
            factor = comes onto price after price impact calculation. Dependent on country
            
            
             */
            

            select factor -- gets factor per shop
            from sockelsat_shop
            where id_shop = id_shop_counter
            into factor;


            open product_cursor;
            read_loop_product:
            LOOP
                fetch product_cursor into id_attribute, price_impact; -- gets price impact and the product attribute

                if doneproduct = true then
                    leave read_loop_product;
                end if;

                update sockelsat_product_attribute_shop -- updates the data
                set price = stdPrice * price_impact * factor
                where id_shop = id_shop_counter
                  and id_product_attribute = id_attribute;
            end loop;
            close product_cursor;

            set factor = 1.00; -- resets factor for the next shop
            set price_impact = 1.00; -- resets price impact per item
            set doneproduct = false;
            set id_shop_counter = id_shop_counter + 1;


        END WHILE label1;
END;

here's some data on the sockelsat_product_attribute_shop

this goes on and on for around 156 product_attributes. But there's also 6 copies of each one according to color, and 14 more copies due to the id_shop, but the id_shop increments by 1. from 1 all the way to 16, with id_shop 13 and 15 being inactive, so it won't do any updates.

the stdPrice is standard as is in the name, which is 229. the price impact changes per item, and can range anywhere from -0.3000 to 15.0000. This is a flat modifier for the size of an item thrown over the base price. Same with factor, this is for shipping. Some countries will have more expensive shipping, which can range from 1 to 15.

I've tried to tweak the update statement. But any changes to the where clause will give me the following error: Error Code: 1048. Column 'price' cannot be null. I presume that this is because I am not being clear enough on what should be updated in the db.

If I am very specific on what should be updated in the DB, I get this error: 0 row(s) affected. The cursor should in theory always get the id_product_attribute, right? Even if it changes per item? How should I go along handling this?

Instead of updating 0 rows, I am expecting to see a query that takes over at least 10 seconds to complete, if I am comparing it to other procedures we have on this db, I would expect it to take at least a minute or 2-3 and state that around 13.000 rows of data would be affected.

Again, I have practically no experience with cursors, so that may also be the root cause of the issue that I am experiencing.

I also hope that it's understandable that I do not want to use a massive set statement, especially seeing how we're going to be moving all our products to use the method of pricing that requires this stored procedure. So it'll probably be handling around 100K items total if that is the case. A set statement would be incredibly impractical

-- EDIT 23/04/2025 why do I not need custom handling? Every product (that is in the sockesat_product_attribute) has a custom price factor as we call it that is multiplied over the standard price. The join that is provided by @Charlieface doesn't give me all the results I need, I need around 2-3K results from the join alone, but his update statement (i tweaked it to be a select statement for debugging purposes) gives me ~150 results. The issue lies with the fact that sockelsat_product_attribute only has the price multipliers for shop 1, but I need those multipliers to also make changes in shops 2 through to 16

Seeing how I don't want a singular flat update to go over every item, but give every item its own special price calculation. I have realised that I will probably have to work with our reference table, as that contains the id_product and the measurements. It's set up as the following id_product-measurement. While the id_product_attribute is completely unique for every item in every store, so there are NO duplicates of those, the reference of an item is the same across shops. The reference of each product is unique in terms of measurements, but there are duplicates due to every shop having every reference of articles that have id_product = 512020060, as those are the measurements. Thank you people genuinely for your help and understanding!

First rule of SQL: any time you find yourself writing a cursor or a loop, and certainly multiple thereof, question your code very carefully. They are very rarely needed, and most uses are slow and often incorrect.

You don't need a while loop or a cursor. You can just update all the rows in a single joined update:

update sockelsat_product_attribute_shop pas
join sockelsat_product_attribute pa on pa.id_attribute = pas.id_product_attribute
join sockelsat_product sp on sp.id_product = pa.id_product
join sockelsat_shop s on s.id_shop = pas.id_shop
set
    pas.price = sp.stdPrice * ifnull(pa.price_impact, 1.0) * ifnull(s.factor, 1.0),
    pas.weight = sp.weight
where sp.id_product = id_product_in
;