Cleanly Delete all Orders, Sales & Customer Data in Magento
Prior to launching a new eCommerce website, at development phase, you’ll always have to carry out testing and this will require creating test orders and customers to ensure that all processes function correctly and as you would expect.
This can often end up leaving a load of test data in the system which you won’t be wanting once the site is launched. Magento is notorious for inserting data in multiple database tables so we’ve provided the SQL query below that will cleanly delete all this data from your Magento database (and reset the increment counters for sales, invoices, customers and shipping).
This has been tested and confirmed working on Magento versions from 1.4.0.1 to 1.9.1.1.
# Tested on Magento CE 1.4.1.1 - 1.9.1.1 SET FOREIGN_KEY_CHECKS=0; ############################## # SALES RELATED TABLES ############################## TRUNCATE `sales_flat_creditmemo`; TRUNCATE `sales_flat_creditmemo_comment`; TRUNCATE `sales_flat_creditmemo_grid`; TRUNCATE `sales_flat_creditmemo_item`; TRUNCATE `sales_flat_invoice`; TRUNCATE `sales_flat_invoice_comment`; TRUNCATE `sales_flat_invoice_grid`; TRUNCATE `sales_flat_invoice_item`; TRUNCATE `sales_flat_order`; TRUNCATE `sales_flat_order_address`; TRUNCATE `sales_flat_order_grid`; TRUNCATE `sales_flat_order_item`; TRUNCATE `sales_flat_order_payment`; TRUNCATE `sales_flat_order_status_history`; TRUNCATE `sales_flat_quote`; TRUNCATE `sales_flat_quote_address`; TRUNCATE `sales_flat_quote_address_item`; TRUNCATE `sales_flat_quote_item`; TRUNCATE `sales_flat_quote_item_option`; TRUNCATE `sales_flat_quote_payment`; TRUNCATE `sales_flat_quote_shipping_rate`; TRUNCATE `sales_flat_shipment`; TRUNCATE `sales_flat_shipment_comment`; TRUNCATE `sales_flat_shipment_grid`; TRUNCATE `sales_flat_shipment_item`; TRUNCATE `sales_flat_shipment_track`; TRUNCATE `sales_invoiced_aggregated`; # ?? TRUNCATE `sales_invoiced_aggregated_order`; # ?? TRUNCATE `log_quote`; ALTER TABLE `sales_flat_creditmemo_comment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_creditmemo_grid` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_creditmemo_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_invoice` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_invoice_comment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_invoice_grid` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_invoice_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order_address` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order_grid` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order_payment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order_status_history` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_shipment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_shipment_comment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_shipment_grid` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_shipment_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_shipment_track` AUTO_INCREMENT=1; ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1; ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1; ALTER TABLE `log_quote` AUTO_INCREMENT=1; ######################################### # DOWNLOADABLE PURCHASED ######################################### TRUNCATE `downloadable_link_purchased`; TRUNCATE `downloadable_link_purchased_item`; ALTER TABLE `downloadable_link_purchased` AUTO_INCREMENT=1; ALTER TABLE `downloadable_link_purchased_item` AUTO_INCREMENT=1; ######################################### # RESET ID COUNTERS ######################################### TRUNCATE `eav_entity_store`; ALTER TABLE `eav_entity_store` AUTO_INCREMENT=1; ############################## # CUSTOMER RELATED TABLES ############################## TRUNCATE `customer_address_entity`; TRUNCATE `customer_address_entity_datetime`; TRUNCATE `customer_address_entity_decimal`; TRUNCATE `customer_address_entity_int`; TRUNCATE `customer_address_entity_text`; TRUNCATE `customer_address_entity_varchar`; TRUNCATE `customer_entity`; TRUNCATE `customer_entity_datetime`; TRUNCATE `customer_entity_decimal`; TRUNCATE `customer_entity_int`; TRUNCATE `customer_entity_text`; TRUNCATE `customer_entity_varchar`; TRUNCATE `tag`; TRUNCATE `tag_relation`; TRUNCATE `tag_summary`; TRUNCATE `tag_properties`; ## CHECK ME TRUNCATE `wishlist`; TRUNCATE `log_customer`; ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1; ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1; ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1; ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1; ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1; ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1; ALTER TABLE `customer_entity` AUTO_INCREMENT=1; ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1; ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1; ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1; ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1; ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1; ALTER TABLE `tag` AUTO_INCREMENT=1; ALTER TABLE `tag_relation` AUTO_INCREMENT=1; ALTER TABLE `tag_summary` AUTO_INCREMENT=1; ALTER TABLE `tag_properties` AUTO_INCREMENT=1; ALTER TABLE `wishlist` AUTO_INCREMENT=1; ALTER TABLE `log_customer` AUTO_INCREMENT=1; ############################## # ADDITIONAL LOGS ############################## TRUNCATE `log_url`; TRUNCATE `log_url_info`; TRUNCATE `log_visitor`; TRUNCATE `log_visitor_info`; TRUNCATE `report_event`; TRUNCATE `report_viewed_product_index`; TRUNCATE `sendfriend_log`; ### ??? TRUNCATE `log_summary` ALTER TABLE `log_url` AUTO_INCREMENT=1; ALTER TABLE `log_url_info` AUTO_INCREMENT=1; ALTER TABLE `log_visitor` AUTO_INCREMENT=1; ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1; ALTER TABLE `report_event` AUTO_INCREMENT=1; ALTER TABLE `report_viewed_product_index` AUTO_INCREMENT=1; ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1; ### ??? ALTER TABLE `log_summary` AUTO_INCREMENT=1; SET FOREIGN_KEY_CHECKS=1;
There is actually an extension that has surfaced now which allows you to erase orders from the Magento back office. Presumably this cleans up as necessary in the database when you delete an order but please be warned, we have not used or tested this extension yet so use at your own risk (backup your database before installing/using).
If you’ve used this extension, please leave a comment with your experience below.
Great list, thank you! I also wanted the search terms cleared out from the dashboard, so I ran the following in addition:
TRUNCATE `catalogsearch_query`;
TRUNCATE `catalogsearch_fulltext`;
TRUNCATE `catalogsearch_result`;
ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1;
ALTER TABLE `catalogsearch_fulltext` AUTO_INCREMENT=1;
ALTER TABLE `catalogsearch_result` AUTO_INCREMENT=1;
TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;
TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;
TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;
ALTER TABLE `sales_bestsellers_aggregated_daily` AUTO_INCREMENT=1;
ALTER TABLE `sales_bestsellers_aggregated_monthly` AUTO_INCREMENT=1;
ALTER TABLE `sales_bestsellers_aggregated_yearly` AUTO_INCREMENT=1;
Thanks for the comment Nick, yes, you’re suggestion code would be useful to clear out any of the data in the search terms and bestsellers, thanks for providing the code for this.
Many thanks for this – works a treat, this has solved a real headache for us.
You’re welcome Tony, glad it helped…
Thank you for the great work.
One question, I run this SQL script plus the comment from Nick in Magento 1.9.1.0 and it worked fine but then in few minutes later slowly started to update the “Search Terms” and few “Products in carts” that was from before.When I first installed the theme to Magento I had to import some files including test orders and etc from PhpMyAdmin.
Hey,
Sounds like you may have some SQL script that re-ran from when you imported the dummy data initially which re-imported the search terms data back in to your database again?
Hard to say to be honest without knowing your exact set up, configuration etc.
Perhaps post the question specifying all details to Magento SE and there’ll be someone that will be able to discuss further and shed some light on this for you most likely.
Thanks for stopping by and taking the time to comment with your results post actioning this query.
Thank you, worked like a charm!
Excellent, glad that helped you out Diego, thanks for stopping by.
Can I use this to Magento 1.9.1.0 or I need to run another command?
Hey Kátia, yes, the above query will also work in Magento 1.9.1.0 🙂
Thanks for this – we’ve used this in our work flow to quickly deploy a staging server which exactly mirrors our live site, minus all the customer data. Works a treat on Magento 1.7.0.2.
Cheers, Tim.
Good stuff Tim, glad you found it useful.
Also, you might want to include this, which clears the newsletter subscribers too:
Thanks for the contribution Tim, indeed, this could also prove useful for clearing our test/dummy data. Will see if we can get the post updated with this too. Thanks.
Thank you sharing your experience on how to tackle problem in database. Those who do not have any idea about running that queries, can remove orders free downloading Store Manager for Magento version
Thank you for stopping by and leaving a comment Maria but I cannot recommend Store Manager for Magento in the slightest. It is buggy, not very user friendly, causes countless issues and is incredibly overpriced for the hassle that the software causes.
We’ve numerous clients that can vouch for that feedback.
It’s a shame that in the 3+ years I know of that Store Manager has been available on the market, it has not improved and rectified these bugs/glitches.
Hello Geoff. Even Magento itself has bugs. The question is how quickly they are solved and what the features are included. We are always trying to improve Store Manager for Magento and eliminate the issues occurring. We are interested in improving the app on the basis your feedback, so if you remember what issues you or your clients encountered, we can provide you free copy for the pointing us in the right direction. If you are interested, e-mail us to contact@mag-manager.com
it’s not working on 1.9.2.2
please help! 🙂
I don’t suppose you could be a little more specific could you K Khan?
What exactly isn’t working for you, what were you trying to remove/delete?
Jackson you are the best, and every other person that contributed so far to this… You really safe me with the script..much love
No problem Hykont, glad to have helped.
Many thanks !
But I also want to share an available Magento extension to help delete orders quickly and easily
http://bsscommerce.com/magento-delete-order-extension.html
Hope it is also useful!
Thank for great infor!
For amateur user who not familiar with coding process, I suggest a great extension that work well on both Magento 1 and 2. Managing orders in backend becomes easier than ever with this helpful tool!
For Magento 1: http://bsscommerce.com/magento-delete-order-extension.html
For Magento 2: http://bsscommerce.com/delete-order-for-magento-2.html
Thanks for suggesting these extensions Jonathan. I have no experience of using these extensions so cannot vouch for them or comment.
For the benefit of other visitors though, the recommended extensions by Jonathan only deletes orders whereas the above SQL does a lot more. As always, approach any third party extensions with caution and take full backups as always before first use.
Thank you very much, it has served me and it is very useful.
I need if it is possible to use the same table deletion but in a certain date range; that is, I need to use those same queries but to erase all data prior to 2017
Is this possible to do ?
Thank you very much in advance
Hey Roberto,
That won’t be easy with SQL queries as there are many tables referenced above they use relationships with other tables (not all having a column for date/time inserted).
So whilst for some columns, you could create individual queries, it would leave a lot of zombie data in the database still where you can’t see when the records were created.
Instead, you’d be better off to write a script which you can run which will iterate through whatever you set and delete the records accordingly associated to it.
For example, here would be a good starting point.
Hope that helps. And good luck!