1

Im trying to do a procedure to update an (for now) empty column recently added.

In this new column, i will put the values of the result obtained by a join query. To explain it better, i have this

delimiter // 
create procedure update_amount_products (in id int)
begin
update categories set products_amount= 
           (SELECT DISTINCT COUNT( products_to_categories.products_id ) 
FROM categories_description
INNER JOIN products_to_categories ON products_to_categories.categories_id = categories_description.categories_id
INNER JOIN products_description ON products_description.products_id = products_to_categories.products_id
WHERE categories_description.categories_id =id)
WHERE categories_id = id;
end
//
call update_amount_products(id);

So what i want, is to not calling the procedure everytime with diferents "ids", i want to update all at once (because there are over 1500 rows with diferents ids). What can i do?

I have been thinking of some kind of "for" to get all the ids and saving them on a variable so later used on my update procedure, but i've seen that there isn't any array possibilty in sql nor foreach.

Any help is highly appreciate it.

1

Finally i got it, after several tries i manage to get what i needed, so i'll share my answer.

First i made one procedure to update one single row in the column:

delimiter //
create procedure update_amount_products (in id int)
begin
update categories set products_amount= 
           (SELECT DISTINCT COUNT( products_to_categories.products_id ) 
FROM categories_description
INNER JOIN products_to_categories ON products_to_categories.categories_id = categories_description.categories_id
INNER JOIN products_description ON products_description.products_id = products_to_categories.products_id
WHERE categories_description.categories_id =id)
WHERE categories_id = id;
end
//

DELIMITER ;

Then i realized i could handle the whole column by calling the procedure with another procedure with a loop on it

DELIMITER $$
CREATE PROCEDURE update_products_amount()

   BEGIN
      DECLARE a INT Default 1 ;
      DECLARE category_length int;

      select max(categories_id) into category_length from categories_description;

      simple_loop: LOOP
         SET a=a+1;

         call update_amount_products(a);

         IF a >= category_length THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;
END $$
DELIMITER ;

commit;

Finally, all i have to do now, is to call my last procedure and voilá, all my column will be automatically updated.

call update_products_amount();
0

Not quite sure what you are trying to achieve bit the UPDATE statement would appear to do the job

To update a single known product:

UPDATE PRODUCTS SET PRODUCT_AMOUNT = 42 WHERE PRODUCTS_ID = 1

You can update several at once depending on the WHERE clause and you can also set it to a value from another table.

  • Hi, thanks for the reply, and what i want to do is to update a entire column which has more than 1000 values with another values i got from an inner join query, i just can't write 1200 times "products_id = x ". – Nighthunter22 Apr 20 '15 at 11:19
  • You may try this as stored procedure body : UPDATE categories SET products_amount = (SELECT COUNT( DISTINCT products_to_categories.products_id ) FROM categories_description INNER JOIN products_to_categories ON products_to_categories.categories_id = categories_description.categories_id WHERE categories_description.categories_id = categories.id GROUP BY categories_description.categories_id) – DeadEye Apr 21 '15 at 9:12
  • @DeadEye Can you explain a bit more please? What you trying to do with that? – Nighthunter22 Apr 21 '15 at 9:23
  • The UPDATE clause will check line by line each of your category. For each category, we get the number of unique products we have for this category (done with the WHERE categories_description.categories_id = categories.id). With this query, your stored procedure does not need any argument. – DeadEye Apr 21 '15 at 9:26
  • @DeadEye I've been testing it til now, it doesn't work because (error msg) it says 'Operand should contain 1 column(s)'. Thats somehow the select i had before, i mean it's only fine as select, but not for update, since you can't know which amount is for which row – Nighthunter22 Apr 21 '15 at 10:03

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.