How To: Bulk delete orders in uCommerce

Sometimes things go wrong in testing but you don't want to delete all orders so here's a little script that will help you quickly delete orders based on your own criteria. In this example I'm deleting orders from customers without a billing email address but you can change the select statement to anything you like.

DECLARE @OrderIds TABLE (OrderId int)
INSERT INTO @OrderIds (OrderId)
-- Change this to whatever select statement you need
SELECT DISTINCT po.OrderId
FROM uCommerce_PurchaseOrder po LEFT JOIN uCommerce_OrderAddress oa ON po.BillingAddressId = oa.OrderAddressId 
WHERE oa.EmailAddress = ''

BEGIN TRAN

UPDATE a SET ShipmentId = NULL FROM uCommerce_OrderLine a WHERE OrderId IN (SELECT OrderId FROM @OrderIds)
UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderId IN (SELECT OrderId FROM @OrderIds)
DELETE a FROM uCommerce_Shipment a WHERE OrderId IN (SELECT OrderId FROM @OrderIds)
DELETE a FROM uCommerce_OrderAddress a WHERE OrderId IN (SELECT OrderId FROM @OrderIds)
DELETE a FROM uCommerce_OrderProperty a WHERE OrderId IN (SELECT OrderId FROM @OrderIds)
DELETE a FROM uCommerce_OrderLine a WHERE OrderId IN (SELECT OrderId FROM @OrderIds)
DELETE pp FROM uCommerce_PaymentProperty pp JOIN uCommerce_Payment p ON pp.PaymentId = p.PaymentId WHERE p.OrderId IN (SELECT OrderId FROM @OrderIds)
DELETE a FROM uCommerce_Payment a WHERE OrderId IN (SELECT OrderId FROM @OrderIds)
DELETE a FROM uCommerce_OrderStatusAudit a WHERE OrderId IN (SELECT OrderId FROM @OrderIds)
DELETE FROM uCommerce_PurchaseOrder WHERE OrderId IN (SELECT OrderId FROM @OrderIds)

--Uncomment this
--COMMIT TRAN

--And comment out this
ROLLBACK TRAN

Author

Tim

comments powered by Disqus