you can Easily Delete orders and manage all orders via Order book extension
check this out
http://www.magentocommerce.com/magento-connect/Modulesoft+Solutions/extension/4047/mspl_orderbook

found this one on a german site…… for magento 1.4.1.x

i am going to try this on my test magento shop, will let you know if it worked….

– Reset Magento TEST Data
SET FOREIGN_KEY_CHECKS=0;
– reset dashboard search queries
TRUNCATE `catalogsearch_query`;
ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1;
– reset sales order info
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 `sales_order_aggregated_created`;
TRUNCATE `sendfriend_log`;
TRUNCATE `tag`;
TRUNCATE `tag_relation`;
TRUNCATE `tag_summary`;
TRUNCATE `wishlist`;
TRUNCATE `log_quote`;
TRUNCATE `report_event`;
ALTER TABLE `sales_flat_creditmemo` AUTO_INCREMENT=1;
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 `sales_order_aggregated_created` 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;
SET FOREIGN_KEY_CHECKS=1;

oh,it show error on my magento 1.4.1.0.
here is error:

Error

SQL query:

– RESET dashboard search queries TRUNCATE `catalogsearch_query` ;

MySQL said: Documentation
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘– reset dashboard search queries
TRUNCATE `catalogsearch_query`’ at line 1

Works fine for me on 1.4.1.0, just remove the comment lines starting with “-” before running the query~

Thanks to Jordi Bos.

thanks for the sql script Jordi, it works like a charm…

it broke my site, lucky i had backup. It was staging site anyway for just testing.

This works PERFECT for 1.3.2.4!!! At last all my test orders gone

Attention though: all my test clients are gone as well, so if you run this with an already working site, delete the steps that deletes your clients

S

O yeah, small detail, it could be me, but when you’re running some other applications on it, these are not working anymore you have to re-install all!

It works perfect on 1.4.1.1

Many thanks!!

Thanks a lot Jordi

Works perfectly on 1.4.1.1

Thanks a lot. I used the query in the comment from @Jordi Bos. Next to the tables that are truncated there, I found a view more tables that needs to be truncate:

TRUNCATE `sales_bestsellers_aggregated_daily`;
TRUNCATE `sales_bestsellers_aggregated_monthly`;
TRUNCATE `sales_bestsellers_aggregated_yearly`;
TRUNCATE `sales_order_tax`;

@mrt: I did not discover any issues…

Many thanks! Exacly what I was searching for!

You could use jordi/luc queries in a controller.

Then add this one to select object in order grid.

It works for me

You should create your own module, with a controller that recall these queries.

In file IndexController create IndexAction and refill checked order Ids with:

$this->getRequest()->getPost('order_ids', array())

Now you can add order Ids to these global queries: in this way you’ll delete only orders checked in order grid.

Hope it’s clear… english is not my native language!

Thanks Christchurch Web Design for the 1.5.0.1 update of the code!

Since Magento change the schema more frequently then there are seasons in a year, I gave up trying to understand how to clean the database. I am contacting the guys of http://www.asulpunto.com and ask them to do the clean up themselves for me. They do not charge a lot and keep backups of the original database too.

The script worked like a charm but it left the return invoices untouched and when I try top open those it results in a long list of errors (since it cannot find the originating order data).

Hey Inchoo,
We ran this query and we are having some very strange behavior attached to the first customer to place an order in our store.

Dashboard shows Brandon Bannerman:
Number of Orders: 2597
Average Order Amount: $22.25
Total Order Amount: $57,774.49

Click on his name from the customer tab on the dash board and it opens the screen to create a new customer.

When you search for him he only has four orders associated with his name:
April 27th – Grand Total $30.59 (Order #1)
May 15th – Grand Total $32.59
May 21st – Grand Total $35.78
May 30th – Grand Total $50.00

Magento will not create an account for him. He has tried 3-4 times and then when he goes back to log in Magento reports there is no user associated with that email address.

And when you search customers on the admin side there is no one with his name or email address.

Do you think it has anything to do with the query?

@Cherie
this is a bug in magento. We have the same issue and we found that on the dashbord magento stores all guest order under one customer (guest or not). Couse magento can not find information about this customer he send you to “Create Customer” Page. Don’t worry this has nothing to do with the query

Hi,

Did anyone found a way to delete best seller module statistics on the dashboard for 1.5.0.1 ?
That would be very helpful,

Many thanks.

Something like this seemed to work for me.

$order = Mage::getModel('sales/order')->loadByIncrementId(100001436);
$order->delete();

What about the sales_tax_item table? I have received duplicate key errors if I don’t remove all items from this table too.

Yup, these is a very helpful script.

There is also a query you can use directly to the MySQL database.

Take a look here

http://wabism.com/magento-clear-all-orders-and-customers/

Great post and great script. This was tremendously helpful. The only thing I thing is missing would be a way to start the order/invoice numbers from 0 again. I did this through phpMyAdmin by running:

TRUNCATE `eav_entity_store`;
ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;

I hope this helps anyone having the same issue. Thanks again!