仙台城 – 伊達獨眼龍政宗

In your Magento development period, you may create many order to test the checkout process. Before the website launch, you should find that there is no “delete” action for orders. What? Is it crazy?

Unfortunately, it’s really crazy (maybe) that you cannot delete order in Magento by default. Like me, there are over hundred orders leave in backend which make our sales confused. In this case, you can execute following SQL statements to delete the ALL orders in MySQL database:

TRUNCATE TABLE {prefix}sales_flat_order;
TRUNCATE TABLE {prefix}sales_order_tax;
TRUNCATE TABLE {prefix}downloadable_link_purchased;
DELETE FROM {prefix}eav_entity_store WHERE entity_type_id IN (SELECT entity_type_id FROM {prefix}eav_entity_type WHERE entity_type_code IN ('order', 'invoice', 'creditmemo', 'shipment'));

Then go to backend, then Reports > Refresh Statistics, select all items and run Refresh Lifetime Statistics.

But wait, most viewed products and bestseller haven’t reset yet. How?

Fine, here is the SQL statement to reset the most viewed products report:

DELETE FROM {prefix}report_event WHERE event_type_id IN (SELECT event_type_id FROM {prefix}report_event_types WHERE event_name IN ('catalog_product_view'));

If you need to delete other reports, you can add the following types to the previous SQL construction in last WHERE statement: ‘sendfriend_product’, ‘catalog_product_compare_add_product’, ‘checkout_cart_add_product’, ‘wishlist_add_product’, ‘wishlist_share’. These types are easily to find in {prefix}report_event_types table.

To truncate Bestsellers you will need to truncate the table ‘sales_bestsellers_aggregated_daily’, ‘sales_bestsellers_aggregated_monthly’ and ‘sales_bestsellers_aggregated_yearly’. Here are the SQL statements to truncate them:

TRUNCATE TABLE {prefix}sales_bestsellers_aggregated_daily;
TRUNCATE TABLE {prefix}sales_bestsellers_aggregated_monthly;
TRUNCATE TABLE {prefix}sales_bestsellers_aggregated_yearly;

That is. It’s not easy job and you may have to get some basic SQL / MySQL knowledge. Of course, using phpMyAdmin is the better way.

Thanks for Magento Support for this help.

Happy Magento’ing ~

Filed under:
Magento, PHP, Web Development by Michael Wai