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
Liked this post? Got a suggestion? Leave a comment