Deleting Order and Customer Data with SQL

Last modified by rickahontas on Wed, March 2, 2011 18:24
Source|Old Revisions  

Last updated for Magento CE 1.4.1.1

This is a work-in-progress to assemble and keep current, the SQL required to perform these actions which are currently not part of the Magento core package but which are often useful for testing and debugging.

Please feel free to contribute or discuss (http://www.magentocommerce.com/boards/viewthread/202142/)

Remember to back up your database before using any of these scripts!

Delete *All* Order & Customer Data

  1. # Magento CE 1.4.1.1
  2. # Be sure to replace "#_" with your table prefix
  3.  
  4. SET FOREIGN_KEY_CHECKS=0;
  5.  
  6. ##############################
  7. # SALES RELATED TABLES
  8. ##############################
  9. TRUNCATE `#_sales_flat_creditmemo`;
  10. TRUNCATE `#_sales_flat_creditmemo_comment`;
  11. TRUNCATE `#_sales_flat_creditmemo_grid`;
  12. TRUNCATE `#_sales_flat_creditmemo_item`;
  13. TRUNCATE `#_sales_flat_invoice`;
  14. TRUNCATE `#_sales_flat_invoice_comment`;
  15. TRUNCATE `#_sales_flat_invoice_grid`;
  16. TRUNCATE `#_sales_flat_invoice_item`;
  17. TRUNCATE `#_sales_flat_order`;
  18. TRUNCATE `#_sales_flat_order_address`;
  19. TRUNCATE `#_sales_flat_order_grid`;
  20. TRUNCATE `#_sales_flat_order_item`;
  21. TRUNCATE `#_sales_flat_order_payment`;
  22. TRUNCATE `#_sales_flat_order_status_history`;
  23. TRUNCATE `#_sales_flat_quote`;
  24. TRUNCATE `#_sales_flat_quote_address`;
  25. TRUNCATE `#_sales_flat_quote_address_item`;
  26. TRUNCATE `#_sales_flat_quote_item`;
  27. TRUNCATE `#_sales_flat_quote_item_option`;
  28. TRUNCATE `#_sales_flat_quote_payment`;
  29. TRUNCATE `#_sales_flat_quote_shipping_rate`;
  30. TRUNCATE `#_sales_flat_shipment`;
  31. TRUNCATE `#_sales_flat_shipment_comment`;
  32. TRUNCATE `#_sales_flat_shipment_grid`;
  33. TRUNCATE `#_sales_flat_shipment_item`;
  34. TRUNCATE `#_sales_flat_shipment_track`;
  35. TRUNCATE `#_sales_invoiced_aggregated`;            # ??
  36. TRUNCATE `#_sales_invoiced_aggregated_order`;        # ??
  37. TRUNCATE `#_log_quote`;
  38.  
  39. ALTER TABLE `#_sales_flat_creditmemo_comment` AUTO_INCREMENT=1;
  40. ALTER TABLE `#_sales_flat_creditmemo_grid` AUTO_INCREMENT=1;
  41. ALTER TABLE `#_sales_flat_creditmemo_item` AUTO_INCREMENT=1;
  42. ALTER TABLE `#_sales_flat_invoice` AUTO_INCREMENT=1;
  43. ALTER TABLE `#_sales_flat_invoice_comment` AUTO_INCREMENT=1;
  44. ALTER TABLE `#_sales_flat_invoice_grid` AUTO_INCREMENT=1;
  45. ALTER TABLE `#_sales_flat_invoice_item` AUTO_INCREMENT=1;
  46. ALTER TABLE `#_sales_flat_order` AUTO_INCREMENT=1;
  47. ALTER TABLE `#_sales_flat_order_address` AUTO_INCREMENT=1;
  48. ALTER TABLE `#_sales_flat_order_grid` AUTO_INCREMENT=1;
  49. ALTER TABLE `#_sales_flat_order_item` AUTO_INCREMENT=1;
  50. ALTER TABLE `#_sales_flat_order_payment` AUTO_INCREMENT=1;
  51. ALTER TABLE `#_sales_flat_order_status_history` AUTO_INCREMENT=1;
  52. ALTER TABLE `#_sales_flat_quote` AUTO_INCREMENT=1;
  53. ALTER TABLE `#_sales_flat_quote_address` AUTO_INCREMENT=1;
  54. ALTER TABLE `#_sales_flat_quote_address_item` AUTO_INCREMENT=1;
  55. ALTER TABLE `#_sales_flat_quote_item` AUTO_INCREMENT=1;
  56. ALTER TABLE `#_sales_flat_quote_item_option` AUTO_INCREMENT=1;
  57. ALTER TABLE `#_sales_flat_quote_payment` AUTO_INCREMENT=1;
  58. ALTER TABLE `#_sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;
  59. ALTER TABLE `#_sales_flat_shipment` AUTO_INCREMENT=1;
  60. ALTER TABLE `#_sales_flat_shipment_comment` AUTO_INCREMENT=1;
  61. ALTER TABLE `#_sales_flat_shipment_grid` AUTO_INCREMENT=1;
  62. ALTER TABLE `#_sales_flat_shipment_item` AUTO_INCREMENT=1;
  63. ALTER TABLE `#_sales_flat_shipment_track` AUTO_INCREMENT=1;
  64. ALTER TABLE `#_sales_invoiced_aggregated` AUTO_INCREMENT=1;
  65. ALTER TABLE `#_sales_invoiced_aggregated_order` AUTO_INCREMENT=1;
  66. ALTER TABLE `#_log_quote` AUTO_INCREMENT=1;
  67.  
  68. #########################################
  69. # DOWNLOADABLE PURCHASED
  70. #########################################
  71. TRUNCATE `#_downloadable_link_purchased`;
  72. TRUNCATE `#_downloadable_link_purchased_item`;
  73.  
  74. ALTER TABLE `#_downloadable_link_purchased` AUTO_INCREMENT=1;
  75. ALTER TABLE `#_downloadable_link_purchased_item` AUTO_INCREMENT=1;
  76.  
  77. #########################################
  78. # RESET ID COUNTERS
  79. #########################################
  80. TRUNCATE `#_eav_entity_store`;
  81. ALTER TABLE  `#_eav_entity_store` AUTO_INCREMENT=1;
  82.  
  83.  
  84. ##############################
  85. # CUSTOMER RELATED TABLES
  86. ##############################
  87. TRUNCATE `#_customer_address_entity`;
  88. TRUNCATE `#_customer_address_entity_datetime`;
  89. TRUNCATE `#_customer_address_entity_decimal`;
  90. TRUNCATE `#_customer_address_entity_int`;
  91. TRUNCATE `#_customer_address_entity_text`;
  92. TRUNCATE `#_customer_address_entity_varchar`;
  93. TRUNCATE `#_customer_entity`;
  94. TRUNCATE `#_customer_entity_datetime`;
  95. TRUNCATE `#_customer_entity_decimal`;
  96. TRUNCATE `#_customer_entity_int`;
  97. TRUNCATE `#_customer_entity_text`;
  98. TRUNCATE `#_customer_entity_varchar`;
  99. TRUNCATE `#_tag`;
  100. TRUNCATE `#_tag_relation`;
  101. TRUNCATE `#_tag_summary`;
  102. TRUNCATE `#_tag_properties`;            ## CHECK ME
  103. TRUNCATE `#_wishlist`;
  104. TRUNCATE `#_log_customer`;
  105.  
  106. ALTER TABLE `#_customer_address_entity` AUTO_INCREMENT=1;
  107. ALTER TABLE `#_customer_address_entity_datetime` AUTO_INCREMENT=1;
  108. ALTER TABLE `#_customer_address_entity_decimal` AUTO_INCREMENT=1;
  109. ALTER TABLE `#_customer_address_entity_int` AUTO_INCREMENT=1;
  110. ALTER TABLE `#_customer_address_entity_text` AUTO_INCREMENT=1;
  111. ALTER TABLE `#_customer_address_entity_varchar` AUTO_INCREMENT=1;
  112. ALTER TABLE `#_customer_entity` AUTO_INCREMENT=1;
  113. ALTER TABLE `#_customer_entity_datetime` AUTO_INCREMENT=1;
  114. ALTER TABLE `#_customer_entity_decimal` AUTO_INCREMENT=1;
  115. ALTER TABLE `#_customer_entity_int` AUTO_INCREMENT=1;
  116. ALTER TABLE `#_customer_entity_text` AUTO_INCREMENT=1;
  117. ALTER TABLE `#_customer_entity_varchar` AUTO_INCREMENT=1;
  118. ALTER TABLE `#_tag` AUTO_INCREMENT=1;
  119. ALTER TABLE `#_tag_relation` AUTO_INCREMENT=1;
  120. ALTER TABLE `#_tag_summary` AUTO_INCREMENT=1;
  121. ALTER TABLE `#_tag_properties` AUTO_INCREMENT=1;
  122. ALTER TABLE `#_wishlist` AUTO_INCREMENT=1;
  123. ALTER TABLE `#_log_customer` AUTO_INCREMENT=1;
  124.  
  125.  
  126. ##############################
  127. # ADDITIONAL LOGS
  128. ##############################
  129. TRUNCATE `#_log_url`;
  130. TRUNCATE `#_log_url_info`;
  131. TRUNCATE `#_log_visitor`;
  132. TRUNCATE `#_log_visitor_info`;
  133. TRUNCATE `#_report_event`;
  134. TRUNCATE `#_report_viewed_product_index`;
  135. TRUNCATE `#_sendfriend_log`;
  136. ### ??? TRUNCATE `#_log_summary`
  137.  
  138. ALTER TABLE `#_log_url` AUTO_INCREMENT=1;
  139. ALTER TABLE `#_log_url_info` AUTO_INCREMENT=1;
  140. ALTER TABLE `#_log_visitor` AUTO_INCREMENT=1;
  141. ALTER TABLE `#_log_visitor_info` AUTO_INCREMENT=1;
  142. ALTER TABLE `#_report_event` AUTO_INCREMENT=1;
  143. ALTER TABLE `#_report_viewed_product_index` AUTO_INCREMENT=1;
  144. ALTER TABLE `#_sendfriend_log` AUTO_INCREMENT=1;
  145. ### ??? ALTER TABLE `#_log_summary` AUTO_INCREMENT=1;
  146.  
  147. SET FOREIGN_KEY_CHECKS=1;

Delete a Single Order

This code will remove a single test order and customer. To remove the order without removing the customer, simply cut out the designated section below.

This has only been cursorily tested, especially with regards to shipping, multiple addresses per order and those *_grid tables (see notes below) but seems to work without trouble.

Getting error when trying to run query: #1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’

One other note, if you run the delete customer SQL, you must do so at the same time as the other query as its based on the customer id in the order.

Good luck and please Please let us know how you get on...

  1. # Magento CE 1.4.1.1
  2. ##################################################
  3. # CHANGE THIS VALUE TO YOUR ORDER INCREMENT ID
  4. ###################################################
  5. SET @incId = '100000001';
  6.  
  7. ##################################################
  8. # IMPORTANT INFO
  9. ##################################################
  10. # Assumes unique order ids across all stores
  11. # Does not revert product stock
  12. # Search and replace #_ first
  13.  
  14. # UNRESOLVED QUESTIONS...
  15. # Is order_id in `*_grid` tables the entity_id from `*_order` or `*_order_grid` ??
  16. # *_grid tables have invoice_id and order_id.  Why both?
  17. # Is quote-order always 1 to 1?
  18.  
  19. ##################################################
  20. # GATHER REQUIRED IDs
  21. ##################################################
  22.  
  23. SET @orderId     = (SELECT entity_id     FROM #_sales_flat_order         WHERE increment_id=@incId);
  24. SET @quoteId    = (SELECT quote_id         FROM #_sales_flat_order         WHERE entity_id=@orderId);
  25. SET @customerId    = (SELECT customer_id     FROM #_sales_flat_order         WHERE entity_id=@orderId);
  26.  
  27.  
  28. ##################################################
  29. # DELETE THE ORDER AND ALL RELATED DATA
  30. ##################################################
  31. SET FOREIGN_KEY_CHECKS=0;
  32.  
  33.  
  34. # Be sure to delete the items requiring subqueries before
  35. # the main entity itself.  Otherwise you'll lose the ids
  36. DELETE FROM `#_sales_flat_creditmemo_comment`     WHERE parent_id IN (SELECT entity_id FROM #_sales_flat_creditmemo WHERE order_id=@orderId);
  37. DELETE FROM `#_sales_flat_creditmemo_item`        WHERE parent_id IN (SELECT entity_id FROM #_sales_flat_creditmemo WHERE order_id=@orderId);
  38. DELETE FROM `#_sales_flat_creditmemo`             WHERE order_id=@orderId;
  39. DELETE FROM `#_sales_flat_creditmemo_grid`        WHERE order_id=@orderId;        # Is this the correct orderId??
  40.  
  41. DELETE FROM `#_sales_flat_invoice_comment` WHERE parent_id IN (SELECT entity_id FROM #_sales_flat_invoice WHERE order_id=@orderId);
  42. DELETE FROM `#_sales_flat_invoice_item`     WHERE parent_id IN (SELECT entity_id FROM #_sales_flat_invoice WHERE order_id=@orderId);
  43. DELETE FROM `#_sales_flat_invoice`         WHERE order_id=@orderId;
  44. DELETE FROM `#_sales_flat_invoice_grid`     WHERE order_id=@orderId;
  45.  
  46. 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??
  47. DELETE FROM `#_sales_flat_quote_shipping_rate`    WHERE address_id IN (SELECT address_id FROM #_sales_flat_quote_address WHERE quote_id=@quoteId);
  48. DELETE FROM `#_sales_flat_quote_item_option`     WHERE item_id IN (SELECT item_id FROM #_sales_flat_quote_item WHERE quote_id=@quoteId);
  49. DELETE FROM `#_sales_flat_quote`                 WHERE entity_id=@quoteId;
  50. DELETE FROM `#_sales_flat_quote_address`         WHERE quote_id=@quoteId;
  51. DELETE FROM `#_sales_flat_quote_item`             WHERE quote_id=@quoteId;
  52. DELETE FROM `#_sales_flat_quote_payment`         WHERE quote_id=@quoteId;
  53.  
  54. DELETE FROM `#_sales_flat_shipment_comment`    WHERE parent_id IN (SELECT entity_id FROM #_sales_flat_shipment WHERE order_id=@orderId);
  55. DELETE FROM `#_sales_flat_shipment_item`         WHERE parent_id IN (SELECT entity_id FROM #_sales_flat_shipment WHERE order_id=@orderId);
  56. DELETE FROM `#_sales_flat_shipment_track`         WHERE order_id  IN (SELECT entity_id FROM #_sales_flat_shipment WHERE order_id=@orderId);
  57. DELETE FROM `#_sales_flat_shipment`             WHERE order_id=@orderId;
  58. DELETE FROM `#_sales_flat_shipment_grid`         WHERE order_id=@orderId;
  59.  
  60. DELETE FROM `#_sales_flat_order`                     WHERE entity_id=@orderId;
  61. DELETE FROM `#_sales_flat_order_address`             WHERE parent_id=@orderId;
  62. DELETE FROM `#_sales_flat_order_item`                 WHERE order_id=@orderId;
  63. DELETE FROM `#_sales_flat_order_payment`             WHERE parent_id=@orderId;
  64. DELETE FROM `#_sales_flat_order_status_history`     WHERE parent_id=@orderId;
  65. DELETE FROM `#_sales_flat_order_grid`                 WHERE increment_id=@incId;
  66.  
  67. # Logs
  68. DELETE FROM `#_log_quote` WHERE quote_id=@quoteId;
  69.  
  70.  
  71. ##################################################
  72. # REMOVE CUSTOMER AND RELATED DATA (OPTIONAL)
  73. # This does NOT remove ALL other orders for the
  74. # given customer. Only run this code if you've
  75. # placed a single order with a newly registered
  76. # the customer.
  77. ##################################################
  78.  
  79. # Customer and address data
  80. DELETE FROM `#_customer_address_entity_datetime`     WHERE entity_id IN (SELECT entity_id FROM #_customer_address_entity WHERE parent_id=@customerId);
  81. DELETE FROM `#_customer_address_entity_decimal`     WHERE entity_id IN (SELECT entity_id FROM #_customer_address_entity WHERE parent_id=@customerId);
  82. DELETE FROM `#_customer_address_entity_int`         WHERE entity_id IN (SELECT entity_id FROM #_customer_address_entity WHERE parent_id=@customerId);
  83. DELETE FROM `#_customer_address_entity_text`         WHERE entity_id IN (SELECT entity_id FROM #_customer_address_entity WHERE parent_id=@customerId);
  84. DELETE FROM `#_customer_address_entity_varchar`     WHERE entity_id IN (SELECT entity_id FROM #_customer_address_entity WHERE parent_id=@customerId);
  85. DELETE FROM `#_customer_address_entity` WHERE parent_id=@customerId;
  86.  
  87. DELETE FROM `#_customer_entity`             WHERE entity_id=@customerId;
  88. DELETE FROM `#_customer_entity_datetime`     WHERE entity_id=@customerId;
  89. DELETE FROM `#_customer_entity_decimal`     WHERE entity_id=@customerId;
  90. DELETE FROM `#_customer_entity_int`         WHERE entity_id=@customerId;
  91. DELETE FROM `#_customer_entity_text`         WHERE entity_id=@customerId;
  92. DELETE FROM `#_customer_entity_varchar`     WHERE entity_id=@customerId;
  93.  
  94.  
  95. # Tags
  96. DELETE FROM `#_tag_relation` WHERE customer_id=@customerId;
  97. #DELETE FROM `#_tag`;                # I think first_customer_id should be changed to next customer
  98.                                     # who used it or removed if not with the other two tables updated
  99.                                     # in the later case.  Anyone up for pioneering this?  How does Magento handle it?
  100. #DELETE FROM `#_tag_summary`;       
  101. #DELETE FROM `#_tag_properties`;
  102.  
  103. # Wishlist
  104. DELETE FROM `#_wishlist_item`     WHERE wishlist_id IN (SELECT wishlist_id FROM #_wishlist WHERE customer_id=@customerId);
  105. DELETE FROM `#_wishlist`         WHERE customer_id=@customerId;
  106.  
  107. # Logs
  108. DELETE FROM `#_log_customer` WHERE customer_id=@customerId;
  109.  
  110.  
  111. SET FOREIGN_KEY_CHECKS=1;



 

Magento 2 GitHub Repository

Magento Job Board - Some sort of tag line goes here

Latest Posts| View all Jobs