Contact us on: +44 (0)1432 617 006

The Site Doctor Blog

Footprints in the snow of a warped mind

< Back to Blog

Bulk delete products by SKU in uCommerce

You may already have come across my script to Quickly delete all products and orders from uCommerce but sometimes you need to delete just a set of products by SKU so here's a new one for you

BEGIN TRAN

DECLARE @ProductIds TABLE (Id int)
INSERT INTO @ProductIds (Id)
SELECT p.ProductId 
FROM uCommerce_Product AS p 
WHERE p.Sku IN ('EXAMPLE1','EXAMPLE2')

DELETE prc FROM uCommerce_ProductReviewComment prc JOIN uCommerce_ProductReview AS pr ON prc.ProductReviewId = pr.ProductReviewId WHERE ProductId IN (SELECT Id FROM @ProductIds)
DELETE FROM uCommerce_ProductReview WHERE ProductId IN (SELECT Id FROM @ProductIds)
DELETE uCommerce_ProductRelation WHERE ProductId IN (SELECT Id FROM @ProductIds)
DELETE uCommerce_ProductProperty WHERE ProductId IN (SELECT Id FROM @ProductIds)
DELETE pdp FROM uCommerce_ProductDescriptionProperty pdp JOIN uCommerce_ProductDescription AS pd ON pdp.ProductDescriptionId = pd.ProductDescriptionId WHERE ProductId IN (SELECT Id FROM @ProductIds)
DELETE uCommerce_ProductDescription WHERE ProductId IN (SELECT Id FROM @ProductIds)
DELETE uCommerce_CategoryProductRelation WHERE ProductId IN (SELECT Id FROM @ProductIds)
DELETE uCommerce_PriceGroupPrice WHERE ProductId IN (SELECT Id FROM @ProductIds)
DELETE FROM uCommerce_Product WHERE ProductId IN (SELECT Id FROM @ProductIds)

-- Just double check things have gone
SELECT * FROM uCommerce_Product p WHERE ProductId IN (SELECT Id FROM @ProductIds)

-- For safety's sake, run it in a transaction just in case you change your mind
ROLLBACK TRAN
-- When happy it works, uncomment this line and comment out the ROLLBACK
--COMMIT TRAN
Author: Tim on

Liked this post? Got a suggestion? Leave a comment