[magento] delete an order by sql

 #Magento CE 1.4.1.1
##################################################
# CHANGE THIS VALUE TO YOUR ORDER INCREMENT ID
###################################################
SET @incId = '100002585';

##################################################
# IMPORTANT INFO
##################################################
# Assumes unique order ids across all stores
# Does not revert product stock
# Search and replace  first

# 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 sales_flat_order         WHERE increment_id=@incId);
SET @quoteId    = (SELECT quote_id         FROM sales_flat_order         WHERE entity_id=@orderId);
SET @customerId    = (SELECT customer_id     FROM 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 `sales_flat_creditmemo_comment`     WHERE parent_id IN (SELECT entity_id FROM sales_flat_creditmemo WHERE order_id=@orderId);
DELETE FROM `sales_flat_creditmemo_item`        WHERE parent_id IN (SELECT entity_id FROM sales_flat_creditmemo WHERE order_id=@orderId);
DELETE FROM `sales_flat_creditmemo`             WHERE order_id=@orderId;
DELETE FROM `sales_flat_creditmemo_grid`        WHERE order_id=@orderId;        # Is this the correct orderId??

DELETE FROM `sales_flat_invoice_comment` WHERE parent_id IN (SELECT entity_id FROM sales_flat_invoice WHERE order_id=@orderId);
DELETE FROM `sales_flat_invoice_item`     WHERE parent_id IN (SELECT entity_id FROM sales_flat_invoice WHERE order_id=@orderId);
DELETE FROM `sales_flat_invoice`         WHERE order_id=@orderId;
DELETE FROM `sales_flat_invoice_grid`     WHERE order_id=@orderId;

DELETE FROM `sales_flat_quote_address_item`     WHERE parent_item_id  IN (SELECT address_id FROM sales_flat_quote_address WHERE quote_id=@quoteId);    # deprecated table??
DELETE FROM `sales_flat_quote_shipping_rate`    WHERE address_id IN (SELECT address_id FROM sales_flat_quote_address WHERE quote_id=@quoteId);
DELETE FROM `sales_flat_quote_item_option`     WHERE item_id IN (SELECT item_id FROM sales_flat_quote_item WHERE quote_id=@quoteId);
DELETE FROM `sales_flat_quote`                 WHERE entity_id=@quoteId;
DELETE FROM `sales_flat_quote_address`         WHERE quote_id=@quoteId;
DELETE FROM `sales_flat_quote_item`             WHERE quote_id=@quoteId;
DELETE FROM `sales_flat_quote_payment`         WHERE quote_id=@quoteId;

DELETE FROM `sales_flat_shipment_comment`    WHERE parent_id IN (SELECT entity_id FROM sales_flat_shipment WHERE order_id=@orderId);
DELETE FROM `sales_flat_shipment_item`         WHERE parent_id IN (SELECT entity_id FROM sales_flat_shipment WHERE order_id=@orderId);
DELETE FROM `sales_flat_shipment_track`         WHERE order_id  IN (SELECT entity_id FROM sales_flat_shipment WHERE order_id=@orderId);
DELETE FROM `sales_flat_shipment`             WHERE order_id=@orderId;
DELETE FROM `sales_flat_shipment_grid`         WHERE order_id=@orderId;

DELETE FROM `sales_flat_order`                     WHERE entity_id=@orderId;
DELETE FROM `sales_flat_order_address`             WHERE parent_id=@orderId;
DELETE FROM `sales_flat_order_item`                 WHERE order_id=@orderId;
DELETE FROM `sales_flat_order_payment`             WHERE parent_id=@orderId;
DELETE FROM `sales_flat_order_status_history`     WHERE parent_id=@orderId;
DELETE FROM `sales_flat_order_grid`                 WHERE increment_id=@incId;

# Logs
DELETE FROM `log_quote` WHERE quote_id=@quoteId;


##################################################
# REMOVE CUSTOMER AND RELATED DATA (OPTIONAL)
# This does NOT remove ALL other orders for the
# given customer. Only run this code if you've
# placed a single order with a newly registered
# the customer.
##################################################

# Customer and address data
DELETE FROM `customer_address_entity_datetime`     WHERE entity_id IN (SELECT entity_id FROM customer_address_entity WHERE parent_id=@customerId);
DELETE FROM `customer_address_entity_decimal`     WHERE entity_id IN (SELECT entity_id FROM customer_address_entity WHERE parent_id=@customerId);
DELETE FROM `customer_address_entity_int`         WHERE entity_id IN (SELECT entity_id FROM customer_address_entity WHERE parent_id=@customerId);
DELETE FROM `customer_address_entity_text`         WHERE entity_id IN (SELECT entity_id FROM customer_address_entity WHERE parent_id=@customerId);
DELETE FROM `customer_address_entity_varchar`     WHERE entity_id IN (SELECT entity_id FROM customer_address_entity WHERE parent_id=@customerId);
DELETE FROM `customer_address_entity` WHERE parent_id=@customerId;

DELETE FROM `customer_entity`             WHERE entity_id=@customerId;
DELETE FROM `customer_entity_datetime`     WHERE entity_id=@customerId;
DELETE FROM `customer_entity_decimal`     WHERE entity_id=@customerId;
DELETE FROM `customer_entity_int`         WHERE entity_id=@customerId;
DELETE FROM `customer_entity_text`         WHERE entity_id=@customerId;
DELETE FROM `customer_entity_varchar`     WHERE entity_id=@customerId;


# Tags
DELETE FROM `tag_relation` WHERE customer_id=@customerId;
#DELETE FROM `tag`;                # I think first_customer_id should be changed to next customer
                                    # who used it or removed if not with the other two tables updated
                                    # in the later case.  Anyone up for pioneering this?  How does Magento handle it?
#DELETE FROM `tag_summary`;      
#DELETE FROM `tag_properties`;

# Wishlist
DELETE FROM `wishlist_item`     WHERE wishlist_id IN (SELECT wishlist_id FROM wishlist WHERE customer_id=@customerId);
DELETE FROM `wishlist`         WHERE customer_id=@customerId;

# Logs
DELETE FROM `log_customer` WHERE customer_id=@customerId;


SET FOREIGN_KEY_CHECKS=1;