In parameter of procedure write incId as INT for input parameter.
begin
# Magento CE 1.4.1.1
##################################################
# CHANGE THIS VALUE TO YOUR ORDER INCREMENT ID
###################################################
SET @incId = incId;
##################################################
# IMPORTANT INFO
##################################################
# Assumes unique order ids across all stores
# Does not revert product stock
# Search and replace mage_ with your table starting text.
# UNRESOLVED QUESTIONS...
# Is order_id in `*_grid` tables the entity_id from `*_order` or `*_order_grid` ??
# *_grid tables have invoice_id and order_id. Why both?
# Is quote-order always 1 to 1?
##################################################
# GATHER REQUIRED IDs
##################################################
SET @orderId = (SELECT entity_id FROM mage_sales_flat_order WHERE increment_id=@incId);
SET @quoteId = (SELECT quote_id FROM mage_sales_flat_order WHERE entity_id=@orderId);
SET @customerId = (SELECT customer_id FROM mage_sales_flat_order WHERE entity_id=@orderId);
##################################################
# DELETE THE ORDER AND ALL RELATED DATA
##################################################
SET FOREIGN_KEY_CHECKS=0;
# Be sure to delete the items requiring subqueries before
# the main entity itself. Otherwise you'll lose the ids
DELETE FROM `mage_sales_flat_creditmemo_comment` WHERE parent_id IN (SELECT entity_id FROM mage_sales_flat_creditmemo WHERE order_id=@orderId);
DELETE FROM `mage_sales_flat_creditmemo_item` WHERE parent_id IN (SELECT entity_id FROM mage_sales_flat_creditmemo WHERE order_id=@orderId);
DELETE FROM `mage_sales_flat_creditmemo` WHERE order_id=@orderId;
DELETE FROM `mage_sales_flat_creditmemo_grid` WHERE order_id=@orderId; # Is this the correct orderId??
DELETE FROM `mage_sales_flat_invoice_comment` WHERE parent_id IN (SELECT entity_id FROM mage_sales_flat_invoice WHERE order_id=@orderId);
DELETE FROM `mage_sales_flat_invoice_item` WHERE parent_id IN (SELECT entity_id FROM mage_sales_flat_invoice WHERE order_id=@orderId);
DELETE FROM `mage_sales_flat_invoice` WHERE order_id=@orderId;
DELETE FROM `mage_sales_flat_invoice_grid` WHERE order_id=@orderId;
DELETE FROM `mage_sales_flat_quote_address_item` WHERE parent_item_id IN (SELECT address_id FROM mage_sales_flat_quote_address WHERE quote_id=@quoteId); # deprecated table??
DELETE FROM `mage_sales_flat_quote_shipping_rate` WHERE address_id IN (SELECT address_id FROM mage_sales_flat_quote_address WHERE quote_id=@quoteId);
DELETE FROM `mage_sales_flat_quote_item_option` WHERE item_id IN (SELECT item_id FROM mage_sales_flat_quote_item WHERE quote_id=@quoteId);
DELETE FROM `mage_sales_flat_quote` WHERE entity_id=@quoteId;
DELETE FROM `mage_sales_flat_quote_address` WHERE quote_id=@quoteId;
DELETE FROM `mage_sales_flat_quote_item` WHERE quote_id=@quoteId;
DELETE FROM `mage_sales_flat_quote_payment` WHERE quote_id=@quoteId;
DELETE FROM `mage_sales_flat_shipment_comment` WHERE parent_id IN (SELECT entity_id FROM mage_sales_flat_shipment WHERE order_id=@orderId);
DELETE FROM `mage_sales_flat_shipment_item` WHERE parent_id IN (SELECT entity_id FROM mage_sales_flat_shipment WHERE order_id=@orderId);
DELETE FROM `mage_sales_flat_shipment_track` WHERE order_id IN (SELECT entity_id FROM mage_sales_flat_shipment WHERE order_id=@orderId);
DELETE FROM `mage_sales_flat_shipment` WHERE order_id=@orderId;
DELETE FROM `mage_sales_flat_shipment_grid` WHERE order_id=@orderId;
DELETE FROM `mage_sales_flat_order` WHERE entity_id=@orderId;
DELETE FROM `mage_sales_flat_order_address` WHERE parent_id=@orderId;
DELETE FROM `mage_sales_flat_order_item` WHERE order_id=@orderId;
DELETE FROM `mage_sales_flat_order_payment` WHERE parent_id=@orderId;
DELETE FROM `mage_sales_flat_order_status_history` WHERE parent_id=@orderId;
DELETE FROM `mage_sales_flat_order_grid` WHERE increment_id=@incId;
# Logs
DELETE FROM `mage_log_quote` WHERE quote_id=@quoteId;
SET FOREIGN_KEY_CHECKS=1;
End
Provide Order Id to delete particular order.
Recommended to take backup before doing so.
Found it as well but didn't check it yet, as still deleting orders from backend using above query.
http://www.mgt-commerce.com/magento-delete-orders.html
Thank you for sharing excellent information. Your website is very cool. Fully useful your blog post... Online Shopping Sites In Ahmedabad
ReplyDelete