Fragmented Thought
TwitterGitHub

Purging or Deleting All Magento Orders

By

Published:

Lance Gliser

Heads up! This content is more than six months old. Take some time to verify everything still works as expected.

Done some test orders on your new site, and need them gone? Here's the script we ran for one of our clients. The actual tables required may vary, because each extension can add its own tables you'll need to consider. In this case, beyond core, we had a table from the Amasty Order Attributes extension. Remember, always backup your database before making changes.

SET FOREIGN_KEY_CHECKS = 0;

-- Tables with extra order data
TRUNCATE TABLE amasty_order_flag;

-- Subscriptions
TRUNCATE TABLE aw_sarp_flat_subscriptions;
TRUNCATE TABLE aw_sarp_sequence;
TRUNCATE TABLE aw_sarp_subscription_items;
TRUNCATE TABLE aw_sarp_subscriptions;

-- Shipments
TRUNCATE TABLE data_orders_shipment_details;
TRUNCATE TABLE data_orders_shipments;
TRUNCATE TABLE sales_flat_shipment_track;
TRUNCATE TABLE sales_flat_shipment_comment;
TRUNCATE TABLE sales_flat_shipment_item;
TRUNCATE TABLE sales_flat_shipment_grid;
TRUNCATE TABLE sales_flat_shipment;

-- Billing agreements
TRUNCATE TABLE sales_billing_agreement_order;
TRUNCATE TABLE sales_billing_agreement;

-- Credit memos
TRUNCATE TABLE sales_flat_creditmemo_comment;
TRUNCATE TABLE sales_flat_creditmemo_item;
TRUNCATE TABLE sales_flat_creditmemo_grid;
TRUNCATE TABLE sales_flat_creditmemo;

-- Invoices
TRUNCATE TABLE sales_flat_invoice_comment;
TRUNCATE TABLE sales_flat_invoice_item;
TRUNCATE TABLE sales_flat_invoice_grid;
TRUNCATE TABLE sales_flat_invoice;
TRUNCATE TABLE sales_invoiced_aggregated_order;
TRUNCATE TABLE sales_invoiced_aggregated;

-- Main order tables affected by just creating an order

-- Attributes
TRUNCATE TABLE amasty_amorderattr_order_attribute;
-- Sales Order
TRUNCATE TABLE sales_flat_order_address;
TRUNCATE TABLE sales_flat_order_grid;
TRUNCATE TABLE sales_flat_order_item;
TRUNCATE TABLE sales_flat_order_payment;
TRUNCATE TABLE sales_flat_order_status_history;

-- Initial Quote
TRUNCATE TABLE sales_flat_quote_address;
TRUNCATE TABLE sales_flat_quote_address_item;
TRUNCATE TABLE sales_flat_quote_item;
TRUNCATE TABLE sales_flat_quote_item_option;
TRUNCATE TABLE sales_flat_quote_payment;
TRUNCATE TABLE sales_flat_quote_shipping_rate;
TRUNCATE TABLE sales_flat_quote;

TRUNCATE TABLE sales_order_tax_item;
TRUNCATE TABLE sales_order_tax;

TRUNCATE TABLE sales_payment_transaction;

TRUNCATE TABLE sales_recurring_profile_order;
TRUNCATE TABLE sales_recurring_profile;

-- Main order table
TRUNCATE TABLE sales_flat_order;

-- Clear aggregate tables
TRUNCATE TABLE sales_order_aggregated_updated;
TRUNCATE TABLE sales_order_aggregated_created;
TRUNCATE TABLE sales_refunded_aggregated_order;
TRUNCATE TABLE sales_refunded_aggregated;
TRUNCATE TABLE sales_shipping_aggregated_order;
TRUNCATE TABLE sales_shipping_aggregated;
TRUNCATE TABLE sales_bestsellers_aggregated_yearly;
TRUNCATE TABLE sales_bestsellers_aggregated_monthly;
TRUNCATE TABLE sales_bestsellers_aggregated_daily;

TRUNCATE TABLE tax_order_aggregated_updated;
TRUNCATE TABLE tax_order_aggregated_created;

-- Clear browsing and order event history
DELETE FROM report_event
WHERE event_type_id IN (
	SELECT event_type_id FROM report_event_types WHERE event_name IN (
		'catalog_product_view', 'sendfriend_product', 'catalog_product_compare_add_product',
		'checkout_cart_add_product', 'wishlist_add_product', 'wishlist_share'
	)
);
TRUNCATE TABLE report_compared_product_index;
TRUNCATE TABLE report_viewed_product_index;
TRUNCATE TABLE report_viewed_product_aggregated_daily;
TRUNCATE TABLE report_viewed_product_aggregated_monthly;
TRUNCATE TABLE report_viewed_product_aggregated_yearly;

TRUNCATE TABLE catalogsearch_query;

-- UPDATE eav_entity_store SET incrementy_last_id = 100000000 WHERE entity_type_id = 5
SET FOREIGN_KEY_CHECKS = 1;

UPDATE eav_entity_store
SET increment_last_id = 100000000
WHERE entity_type_id IN (
	SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code IN (
		'order', 'invoice'
	)
);