Deleting your test orders currently requires running a script directly on your Database. Make sure you fully backup your database before doing anything! This script will delete all orders in the database and reset all order counters!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
SET FOREIGN_KEY_CHECKS=0; TRUNCATE `sales_order`; TRUNCATE `sales_order_datetime`; TRUNCATE `sales_order_decimal`; TRUNCATE `sales_order_entity`; TRUNCATE `sales_order_entity_datetime`; TRUNCATE `sales_order_entity_decimal`; TRUNCATE `sales_order_entity_int`; TRUNCATE `sales_order_entity_text`; TRUNCATE `sales_order_entity_varchar`; TRUNCATE `sales_order_int`; TRUNCATE `sales_order_text`; TRUNCATE `sales_order_varchar`; 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_order_item`; TRUNCATE `sendfriend_log`; TRUNCATE `tag`; TRUNCATE `tag_relation`; TRUNCATE `tag_summary`; TRUNCATE `wishlist`; TRUNCATE `log_quote`; TRUNCATE `report_event`; ALTER TABLE `sales_order` AUTO_INCREMENT=1; ALTER TABLE `sales_order_datetime` AUTO_INCREMENT=1; ALTER TABLE `sales_order_decimal` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity_datetime` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity_decimal` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity_int` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity_text` AUTO_INCREMENT=1; ALTER TABLE `sales_order_entity_varchar` AUTO_INCREMENT=1; ALTER TABLE `sales_order_int` AUTO_INCREMENT=1; ALTER TABLE `sales_order_text` AUTO_INCREMENT=1; ALTER TABLE `sales_order_varchar` 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_order_item` AUTO_INCREMENT=1; ALTER TABLE `sendfriend_log` 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 `wishlist` AUTO_INCREMENT=1; ALTER TABLE `log_quote` AUTO_INCREMENT=1; ALTER TABLE `report_event` AUTO_INCREMENT=1; -- reset customers 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 `log_customer`; TRUNCATE `log_visitor`; TRUNCATE `log_visitor_info`; 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 `log_customer` AUTO_INCREMENT=1; ALTER TABLE `log_visitor` AUTO_INCREMENT=1; ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1; -- Reset all ID counters TRUNCATE `eav_entity_store`; ALTER TABLE `eav_entity_store` AUTO_INCREMENT=1; SET FOREIGN_KEY_CHECKS=1; |
Here is the SQL for 4.x
Thanks Martin! Worked great for 1.4.1.1. I had to make a slight change to make it work in phpmyadmin for me. I just had to add “mgn_” in front of each of the databases to make it go through.
for example…
Spankin’ Fantabulous! Much thanks.
Hey thanks for sharing this! huge help. I am learning about Magento now and am stoked about how powerful and feature rich it is, especially for how young of a platform it is. I will be checking out the rest of your blog thanks again! p.s. I came here from Noupe.com
Rock on.
Is this SQL Query still valid for Magento? new Log_Visitor tables got added in version 1.3.1.1, so I don’t this this query will still do it all 100%, it certainly removes the test orders in 1.3.2.2. Just don’t want it missing anything.
Please take a little look.
Thanks,
Rob.
Well check this, a total solution for deleting Magento order “Delete Any Order”
https://www.yireo.com/software/delete-any-order
Excellent!
It works!
Mag. ver. 1.3.2.4
thanks for the tips, its helpful.
Thank you so much. Worked great.
works greet¡¡ thx 4 sharing, but how to export only product, category and attributes in sql mode?
thx
Thank you very much! This has helped me sort out a huge bug in a client’s shop.
thanks! works perfectly
the table “sales_order_tax” should also be cleaned, otherwise you get duplicate tax reported in your backend reports and on the sales order detail page.
Can you please explain how to delete a single order(with increment_id available)?
You da man! Couldn’t get any easier than that.
thanks boss its amazing, helped me a lot…
Wow wonderful, worked like a charm on 1.3.3
Thank you so much!
Hi does this work for 1.4x?
Manage Magento Orders by Orderbook Extension. visit
http://www.magentocommerce.com/magento-connect/Modulesoft+Solutions/extension/4047/mspl_orderbook
Since Magento changed to using flat sales order tables (1.4x) this doesn’t work anymore 🙁
Can you update ur SQL please 🙂
Thanks, some one had posted it on magento forum, and also put the source site name which is your’s thanks.
Is their any way by which we delete a particular order and reset the counter for that particular order. so that the test orders don’t bother the order ID flow
Thanks Martin!!!!
I just used your updated SQL for Magento 1.4.1.1.
It worked perfectly!
Thanks Martin, worked like a charm.
I Got Great Idea after seeing fantastic table concept for sales and order table …
Thanks
Hello,
I am trying to delete test orders from the dashboard in magento. I am very new with this program and I am not sure how to back up and reset the database. I’m using godaddy – sql database but I am not sure how to do it.
I appreciate if someone can help me with this matter.
Thanks so much in Advance,
Tiffany.