Mysql – Delete using id’s not found in loop – MySQL


I have a stored procedure where I loop through an array passed as an argument, update the values provided, then delete the missing values using an id passed in the given array.

    /* edit the purchase items */
        WHILE items_index<items_length DO
            UPDATE PurchaseOrderItems SET quantity=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].quantity')) ,products_productid=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].productid')) WHERE itemid=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].purchaseitemid'));
            IF items_index+1=items_length THEN
                /* condition will only be true in last iteration*/
                SET item_separator='';
            END IF;
            SET save_list = CONCAT(save_list,JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].purchaseitemid')),item_separator);
            SET items_index=items_index+1;               
        END WHILE;
        /* delete all other items that were not in the list */
        DELETE FROM PurchaseOrderItems WHERE purchaseorders_purchaseorderid=p_purchaseorderid AND itemid not in (save_list);

The delete command however deletes all the records except for the first item in the save_list variable. I have confirmed that the variable save_list has the id's to be skipped so where are things going wrong?

Best Answer

Maybe im wrong but the SET item_separator='' looks empty. I could imagine that your ID´s you build there are not right and therefore he does not ignore them.
I'm going to rebuild a scenario like this and try to help you. But first see if the output of save_list is really a string of ID´s.