Please follow directions according to your version of Magento. Backup your database before running these SQL commands. You are responsible for backing up your database before running SQL commands on it. Run the SQL commands from a MySQL GUI Client, web client such as phpMyAdmin, or command-line interface.
Magento 1.4.x
Varien made deleting orders very simple in 1.4 by linking all the related tables together and setting up cascade deletion. Thank you Varien! Now if you would just let us delete orders through the Admin web interface.
SET @orderId = xxxxxxxxx; #replace this WITH your ORDER NUMBER SET FOREIGN_KEY_CHECKS = 1; DELETE FROM sales_flat_quote WHERE reserved_order_id = @orderId; DELETE FROM sales_flat_order WHERE increment_id = @orderId; |
Magento 1.3.x
SET @orderId = xxxxxxxxx; #replace this WITH your ORDER NUMBER SET FOREIGN_KEY_CHECKS = 1; SET @salesFlatQuoteId = ( SELECT entity_id FROM sales_flat_quote WHERE reserved_order_id = @orderId ); SET @salesOrderId = ( SELECT entity_id FROM sales_order WHERE increment_id = @orderId ); /* temp table used as an array */ CREATE TEMPORARY TABLE del_sales( id INT AUTO_INCREMENT PRIMARY KEY, salesId INT(10) ); /* temp table used as an array */ CREATE TEMPORARY TABLE del_statusSales( id INT AUTO_INCREMENT PRIMARY KEY, salesId INT(10) ); INSERT INTO del_statusSales (salesId) SELECT entity_id FROM sales_order_entity_int WHERE VALUE = @salesOrderId AND attribute_id = ANY ( SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'order_id' ) AND entity_id = ANY ( SELECT entity_id FROM sales_order_entity WHERE entity_type_id = ANY ( SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'invoice' OR entity_type_code = 'shipment' OR entity_type_code = 'creditmemo' ) ); INSERT INTO del_sales (salesId) SELECT entity_id FROM sales_order_entity WHERE parent_id = ANY ( SELECT salesId FROM del_statusSales ) AND entity_type_id = ANY ( SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'invoice_item' OR entity_type_code = 'invoice_comment' OR entity_type_code = 'shipment_item' OR entity_type_code = 'shipment_comment' OR entity_type_code = 'shipment_track' OR entity_type_code = 'creditmemo_item' OR entity_type_code = 'creditmemo_comment' ); INSERT INTO del_sales (salesId) SELECT salesId FROM del_statusSales; INSERT INTO del_sales (salesId) SELECT entity_id FROM sales_order_entity WHERE parent_id = @salesOrderId; DELETE FROM sales_order_entity WHERE entity_id = ANY ( SELECT salesId FROM del_sales ); DELETE FROM sales_flat_quote WHERE reserved_order_id = @orderId; DELETE FROM sales_flat_order_item WHERE quote_item_id = @salesFlatQuoteId; DELETE FROM sales_order WHERE increment_id = @orderId; /* drop temp tables */ DROP TEMPORARY TABLE del_sales; DROP TEMPORARY TABLE del_statusSales; |