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

No comments yet.

Leave a Reply