Remove order related information for orders placed before year 2014.
This set of SQL queries is suitable for Magento >= 1.4.1.0
SELECT @max_order_id:=MAX( entity_id ) FROM `sales_flat_order` WHERE created_at < '2014-01-01 00:00:00';
### QUOTE ###
DELETE FROM `sales_flat_quote` as q where q.entity_id IN (SELECT quote_id FROM `sales_flat_order` as o where o.entity_id <= @max_order_id)
DELETE a1
FROM `sales_flat_quote_address` a1
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_quote_item` a1
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_quote_address_item` a1
LEFT JOIN sales_flat_quote_address a2 ON a1.quote_address_id = a2.address_id
LEFT JOIN sales_flat_quote_item a3 ON a1.quote_item_id = a3.item_id
WHERE a2.address_id IS NULL OR a3.item_id IS NULL;
DELETE a1
FROM `sales_flat_quote_item_option` a1
LEFT JOIN sales_flat_quote_item a2 ON a1.item_id = a2.item_id
WHERE a2.item_id IS NULL ;
DELETE a1
FROM `sales_flat_quote_payment` a1
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_quote_shipping_rate` a1
LEFT JOIN sales_flat_quote_address a2 ON a1.address_id = a2.address_id
WHERE a2.address_id IS NULL ;
### CREDITMEMO ###
DELETE FROM `sales_flat_creditmemo` where order_id <= @max_order_id;
DELETE a1
FROM `sales_flat_creditmemo_comment` a1
LEFT JOIN sales_flat_creditmemo a2 ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_creditmemo_grid` a1
LEFT JOIN sales_flat_creditmemo a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_creditmemo_item` a1
LEFT JOIN sales_flat_creditmemo a2 ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
### SHIPMENT ###
DELETE FROM `sales_flat_shipment` where order_id <= @max_order_id;
DELETE a1
FROM `sales_flat_shipment_track` a1
LEFT JOIN sales_flat_shipment a2 ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_shipment_item` a1
LEFT JOIN sales_flat_shipment a2 ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_shipment_grid` a1
LEFT JOIN sales_flat_shipment a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_shipment_comment` a1
LEFT JOIN sales_flat_shipment a2 ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
### INVOICE ###
DELETE FROM `sales_flat_invoice` where order_id <= @max_order_id;
DELETE a1
FROM `sales_flat_invoice_comment` a1
LEFT JOIN sales_flat_invoice a2 ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_invoice_grid` a1
LEFT JOIN sales_flat_invoice a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_invoice_item` a1
LEFT JOIN sales_flat_invoice a2 ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
### ORDER ###
DELETE FROM `sales_flat_order` where entity_id <= @max_order_id;
DELETE a1
FROM `sales_flat_order_address` a1
LEFT JOIN sales_flat_order a2 ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_order_grid` a1
LEFT JOIN sales_flat_order a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_order_item` a1
LEFT JOIN sales_flat_order a2 ON a1.order_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_order_payment` a1
LEFT JOIN sales_flat_order a2 ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_order_status_history` a1
LEFT JOIN sales_flat_order a2 ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_payment_transaction` a1
LEFT JOIN sales_flat_order a2 ON a1.order_id = a2.entity_id
LEFT JOIN sales_flat_order_payment a3 ON a1.payment_id = a3.entity_id
WHERE a2.entity_id IS NULL OR a3.entity_id IS NULL;
This set of SQL queries is suitable for Magento < 1.4.1.0
SELECT @max_order_id:=MAX( entity_id ) FROM `sales_order` WHERE created_at < '2014-01-01 00:00:00';
SELECT @max_quote_id:=value FROM `sales_order_int` o
LEFT JOIN eav_attribute a ON a.attribute_id = o.attribute_id
WHERE o.entity_id = @max_order_id AND a.attribute_code='quote_id';
SELECT @invoice_type_order_attribute:=attribute_id FROM `eav_attribute` WHERE attribute_code='order_id' AND entity_type_id = 16;
SELECT @shipment_type_order_attribute:=attribute_id FROM `eav_attribute` WHERE attribute_code='order_id' AND entity_type_id = 19;
SELECT @creditmemo_type_order_attribute:=attribute_id FROM `eav_attribute` WHERE attribute_code='order_id' AND entity_type_id = 23;
### QUOTE ###
DELETE FROM `sales_flat_quote` WHERE entity_id <= @max_quote_id;
DELETE a1
FROM `sales_flat_quote_address` a1
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_quote_item` a1
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_quote_address_item` a1
LEFT JOIN sales_flat_quote_address a2 ON a1.quote_address_id = a2.address_id
LEFT JOIN sales_flat_quote_item a3 ON a1.quote_item_id = a3.item_id
WHERE a2.address_id IS NULL OR a3.item_id IS NULL;
DELETE a1
FROM `sales_flat_quote_shipping_rate` a1
LEFT JOIN sales_flat_quote_address a2 ON a1.address_id = a2.address_id
WHERE a2.address_id IS NULL ;
DELETE a1
FROM `sales_flat_quote_item_option` a1
LEFT JOIN sales_flat_quote_item a2 ON a1.item_id = a2.item_id
WHERE a2.item_id IS NULL ;
DELETE a1
FROM `sales_flat_quote_payment` a1
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_quote_shipping_rate` a1
LEFT JOIN sales_flat_quote_address a2 ON a1.address_id = a2.address_id
WHERE a2.address_id IS NULL ;
### CREDITMEMO, INVOICE, SHIPMENT ###
DELETE `e`
FROM `sales_order_entity` AS `e`
INNER JOIN `sales_order_entity_int` AS `_table_order_id` ON (_table_order_id.entity_id = e.entity_id) AND (_table_order_id.attribute_id=@invoice_type_order_attribute)
WHERE (e.entity_type_id = '16') AND (_table_order_id.value <= @max_order_id);
DELETE `e`
FROM `sales_order_entity` AS `e`
INNER JOIN `sales_order_entity_int` AS `_table_order_id` ON (_table_order_id.entity_id = e.entity_id) AND (_table_order_id.attribute_id=@shipment_type_order_attribute)
WHERE (e.entity_type_id = '19') AND (_table_order_id.value <= @max_order_id);
DELETE `e`
FROM `sales_order_entity` AS `e`
INNER JOIN `sales_order_entity_int` AS `_table_order_id` ON (_table_order_id.entity_id = e.entity_id) AND (_table_order_id.attribute_id=@creditmemo_type_order_attribute)
WHERE (e.entity_type_id = '23') AND (_table_order_id.value <= @max_order_id);
### CREDITMEMO, INVOICE, SHIPMENT - ITEMS, COMMENTS ###
### SHIPMENT TRACK ###
DELETE `a1`
FROM `sales_order_entity` AS `a1`
LEFT JOIN `sales_order_entity` AS `a2` ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL
AND a1.entity_type_id IN (17, 18, 20, 21, 22, 24, 25);
### ORDER ADDRESS, PAYMENT, STATUS HISTORY ###
DELETE FROM `sales_order_entity` WHERE entity_type_id IN (12,14,15) AND parent_id <= @max_order_id;
### ORDER ENTITY ###
DELETE a1
FROM `sales_order_entity_datetime` a1
LEFT JOIN sales_order_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_order_entity_decimal` a1
LEFT JOIN sales_order_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_order_entity_int` a1
LEFT JOIN sales_order_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_order_entity_text` a1
LEFT JOIN sales_order_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_order_entity_varchar` a1
LEFT JOIN sales_order_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
### ORDER ###
DELETE FROM `sales_order` where entity_id <= @max_order_id;
DELETE a1
FROM `sales_order_datetime` a1
LEFT JOIN sales_order a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_order_decimal` a1
LEFT JOIN sales_order a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_order_int` a1
LEFT JOIN sales_order a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_order_text` a1
LEFT JOIN sales_order a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_order_varchar` a1
LEFT JOIN sales_order a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_order_tax` a1
LEFT JOIN sales_order a2 ON a1.order_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_flat_order_item` a1
LEFT JOIN sales_order a2 ON a1.order_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `sales_payment_transaction` a1
LEFT JOIN sales_order a2 ON a1.order_id = a2.entity_id
WHERE a2.entity_id IS NULL;
Remove customers without orders. Apply customers' deletion script after deleting orders.
Please select the variant matched your Magento version (base on SQL comments bellow)
#--- Only for Magento >= 1.4.1.0 ---#
DELETE FROM `customer_entity` WHERE entity_id NOT IN (SELECT customer_id FROM sales_flat_order);
#--- Only for Magento < 1.4.1.0 --#
DELETE FROM `customer_entity` WHERE entity_id NOT IN (SELECT customer_id FROM sales_order);
DELETE a1
FROM `customer_address_entity` a1
LEFT JOIN customer_entity a2 ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `customer_address_entity_datetime` a1
LEFT JOIN customer_address_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `customer_address_entity_decimal` a1
LEFT JOIN customer_address_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `customer_address_entity_int` a1
LEFT JOIN customer_address_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `customer_address_entity_text` a1
LEFT JOIN customer_address_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `customer_address_entity_varchar` a1
LEFT JOIN customer_address_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `customer_entity_datetime` a1
LEFT JOIN customer_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `customer_entity_decimal` a1
LEFT JOIN customer_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `customer_entity_int` a1
LEFT JOIN customer_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `customer_entity_text` a1
LEFT JOIN customer_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `customer_entity_varchar` a1
LEFT JOIN customer_entity a2 ON a1.entity_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
### WHISHLIST ###
DELETE a1
FROM `wishlist` a1
LEFT JOIN customer_entity a2 ON a1.customer_id = a2.entity_id
WHERE a2.entity_id IS NULL ;
DELETE a1
FROM `wishlist_item` a1
LEFT JOIN wishlist a2 ON a1.wishlist_id = a2.wishlist_id
WHERE a2.wishlist_id IS NULL ;
#--- Only for Magento >= 1.4.1.0 ---#
DELETE a1
FROM `wishlist_item_option` a1
LEFT JOIN wishlist_item a2 ON a1.wishlist_item_id = a2.wishlist_item_id
WHERE a2.wishlist_item_id IS NULL ;