I'm just trying to figure out which SQL query/queries to use to bulk map products to our store's clearance category so I don't have to map 50-100 products individually.
I need to make sure a select list of products are mapped to only one category, and I either get an error message or an "OK" message but the products aren't mapped.
This is what I'm using to unmap specific products from all categories. I use product SKUs because that's what every else uses when giving me a list of items to map. (Those SKUs are just for testing, it's normally dozens of web IDs).
DELETE ProductCategory
FROM ProductCategory
JOIN Product ON ProductCategory.ProductID = Product .ProductID
WHERE Product.SKU IN ('18842', '18231', '18927', '18836')
AND ProductCategory.CategoryID != '448'
And this is what I'm trying to use to map specific products to Clearance. I've modified it several times while to trying to get this to work, but this is the gist of what I want.
INSERT INTO ProductCategory (ProductID, CategoryID)
SELECT ProductCategory.ProductID, '448' FROM ProductCategory
JOIN Product ON ProductCategory.ProductID = Product.ProductID
WHERE Product.SKU IN ('18842', '18231', '18927', '18836')
If I unmap products first, then run the query to put them in clearance, I get an "OK" message, but the products aren't actually mapped anywhere. If I try map them to clearance first, then I get this message:
"Exception=Violation of PRIMARY KEY constraint 'PK_ProductCategory'. Cannot insert duplicate key in object 'dbo.ProductCategory'. The duplicate key value is (137356, 448). The statement has been terminated."
I assume that message pops up because the product/category IDs are already in the table, but I don't know how to work around that. How do I just map them to a specific category?