0

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?

  • Welcome to the site. I think you are missing the sequence of Loop statement. It's DO..While loop or else loop statement. As see (en.wikipedia.org/wiki/Do_while_loop) – Md Haidar Ali Khan Nov 15 '18 at 15:22
  • Suggestion: Don't bury things in JSON if you need MySQL to manipulate them. – Rick James Nov 15 '18 at 23:57
  • @MdHaidarAliKhan the WHILE...DO..END WHILE syntax has been working fine for me. – Eliud Daniels Nov 16 '18 at 14:25
  • @RickJames next time I'm definitely sticking to batch updates – Eliud Daniels Nov 16 '18 at 14:26
-1

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.

  • I am setting the value of item_separator on declaration to a comma. The logic translates as the value of save_list reads fine; all items comma separated apart from the last one. – Eliud Daniels Nov 16 '18 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.