You are viewing an old revision of this post, from February 25, 2015 @ 09:57:05. See below for differences between this version and the current revision.

Cleanly Delete all Orders, Sales & Customer Data in Magento

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.

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;

 

 

 

Revisions

Revision Differences

There are no differences between the February 25, 2015 @ 09:57:05 revision and the current revision. (Maybe only post meta information was changed.)

No comments yet.

Leave a Reply