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.