This is going to be LOOOOONG
One more thing…Is there anyway to update a number in a table by a percentage? Can you just say UPDATE products SET product_price=*1.05 FROM products WHERE product_id=$value; Does that work? Yes. The part in blue you left out...
The end result is to increase the price in the product table by 5%. Is there anyway to use a single SELECT query to retrieve all products selected without making an outrageous number of queries? Since the end result is an UPDATE, maybe even turn this into a single UPDATE query?
SET Product_price = product_price * 1.052
Where product_id = $value
Yes, you can jumble all that into one single update, BUT before I go to far (there are other things I want to ask you about), let me say this...I understand the need to get the price * 1.05 calc, but your requesting a query to UPDATE the price in the table itself. Going off that, you may as well as...
1) Just update all the records in the table right off the bat, or
2) Add a field that contains the price * 1.05 calc in it. (either to the table itself or to the record set)
#1 doesn't seem like the ideal choice, here's why I said that though...when you perform the UPDATE your requesting, your actually going back and touching the table itself, the price in the product table for that particular product_id will stay @ the *1.05 value...so the original price gets totally discarded...and eventually overtime all the prices will come to their price * 1.05 value. You'd force yourself to have to do another UPDATE in order to...
1) Get the product back to it's original price, and
2) To avoid a continuous 5% increment on price. When that product is selected again, the coded UPDATE will exec again, so say the product was selected 4 times in one day, the multiple updates will do --> (price * 1.05) then again, (price *1.05) for the 2nd time it was selected, then again (price *1.05) when selected the 3rd time, see what I'm saying?? I would do #2, or save the calc entirely for the front end. Or if you just want to generate the price * 1.05 calc in the record set, try this...basically the same as above but using a calc to create a field to the record set...
Select distinct SC.Category_id,
P.Price * 1.05 [SalePrice]
from Subcategory_Category SC
join Product_Subcategories PS on SC.Subcategory_id = PS.Subcategory_id
join Product P on PS.Product_id = P.Product_id
where SC.Category_id = @Category_id
and SC.subCategory_id = @subCategory_id
and PS.Product_id = @Product_id