Bulk mapping products with SQL

Question

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?

Answer 1

Your first query is filtering out your clearance category ID when it does the delete, so it leaves those records in the table and your second query is trying to insert values that already exist.  You'll either want to remove that AND clause from the first query so that the products are unmapped from everything first, or add a subselect in the second query to skip trying to insert mappings for products that are already mapped.

Comments

This fixed the issue with the error message, but eRatex still doesn't seem to be mapping the products to the appropriate category. I'll get a "Command Executed Okay" message, but when I check the mappings under manage products, the Clearance category still isn't checked.