Take the 2-minute tour ×
Magento Stack Exchange is a question and answer site for users of the Magento e-Commerce platform. It's 100% free, no registration required.

Which way do you reset magento orders?

For example, now the current order - # 100000051.

I need:

1) completely remove all orders from the Database 
2) new order will have to start with a number # 100000001 (this is important) 

Please let us know your trusty and checked up way! Thank you!

share|improve this question

2 Answers 2

up vote 7 down vote accepted

Run this query in your database,you get expected result

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;
share|improve this answer
    
@GOOD works .give u upvote –  Amit Bera Aug 6 at 10:50
    
Thanks for a great solution! But which version of magento community meets your solution? v.1.8 or 1.9? I applied your commands to my database in SSH (Putty) and my problem is solved. But a number of commands are in error: ERROR 1701 (42000): Can not truncate a table referenced in a foreign key constraint (db_name.sales_payment_transaction, CONSTRAINT FK_DA51A10B240XXXXXXXXXXXX FOREIGN KEY (` payment_id`) REFERENCES db_name These are: TRUNCATE sales_flat_creditmemo; TRUNCATE sales_flat_invoice; TRUNCATE sales_flat_order; TRUNCATE sales_flat_order_item; –  user9714 Aug 7 at 3:55
    
put this code directly in your database check. It works for me correctly.I tried it in 1.7 and 1.8. –  saravanavelu Aug 7 at 4:29
    
put this code directly in sql in your database check. It works for me correctly.I tried it in 1.7 and 1.8. –  saravanavelu Aug 7 at 4:50

Use third party extension for delete all orders from magento system:

http://www.magentocommerce.com/magento-connect/orders-eraser.html Or http://www.magentocommerce.com/magento-connect/delete-orders-6.html

Then truncate sales_flat_order or sales_flat_order_grid or rest increment field value using mysql query

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.