I am trying to write a SP that will update the price of products that are in a particular category.
I have 3 tables;
PRODUCT
- productID, price
CATEGORY
- categoryID, categoryName, parentID
PRODUCT_CATEGORY_MAP
- productID, categoryID
What I want to do, is allow admin to pass is a new price and categoryID, and it will update all the products that are within the supplied category.
I will start with:
UPDATE PRODUCT SET price = @.price WHERE categoryID = @.categoryID
however I do not have categoryID within the PRODUCT table
Do I place a SELECT statement after the WHERE?
DECLARE @.productID int
UPDATE PRODUCT SET price = @.price WHERE (SELECT @.productID = productID FROM PRODUCT_CATEGORY_MAP WHERE categoryID=@.categoryID)
Not sure if that would work or not or if I am on the right track.
Thanks for any help,
Mick
You just join in the other tables like this (given that you only need categoryID and that's in the map table, there is no need to join Category too, although you could if you were filtering on Category name for example)
UPDATE Product SET price = @.price
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
WHERE PCM.categoryID = @.categoryID
Ok, I ended up figuring it out, but using IN instead of INNER JOIN. I like yours better.
So I want to extend this a little more...
Say I have the categories
ID Name ParentID
1 Cat1 0
2 Cat2 1
I have assigned a product to Cat2
productID categoryID
1 2
I now want my SP to work so that if a user selects the category Cat1, it will also update all child products.
I have a SP that gets all the child categories...
SELECT categoryID FROM Category
WHERE categoryID IN (SELECT id FROM dbo.GetChildren(@.categoryID))
I have this so far, but it only updates categories in the selected category, not it's childs as well.
UPDATE Product SET ourUSDPrice = @.newPrice
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
WHERE PCM.categoryID IN
(SELECT categoryID FROM Category C
WHERE C.categoryID IN (SELECT id FROM dbo.GetChildren(@.categoryID))
AND PCM.categoryID=@.categoryID)
Thanks again,
Mick
UPDATE Product SET ourUSDPrice = @.newPrice
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
INNER JOIN dbo.GetChildren(@.categoryID)) as ChildCategory on PCM.categoryID = ChildCategory.Id
--or
UPDATE Product SET ourUSDPrice = @.newPrice
Where Exists (Select ProductId From Product P INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID INNER JOIN dbo.GetChildren(@.categoryID)) as ChildCategory on PCM.categoryID = ChildCategory.Id)
|||Mani has posted two ways to make your query work - do you have the GetChildren function working, so that it gets all the children at all levels? I wasn't sure from your post whether you were asking about that or not.
