Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 2
Deleting Order and Customer Data in Magento 1.4.1.1
 
harikaram
Member
 
Avatar
Total Posts:  54
Joined:  2008-07-27
 

(Latest version on the Wiki: http://www.magentocommerce.com/wiki/import-export_and_data_manipulation/deleting_order_and_customer_data_with_sql)

I’ve just been working on the SQL to clear out all customer and order data from my development installation.  It’s tweaked for 1.4.1.1.  Replace the “#_” with your DB prefix.  Be sure to backup first and let me know if there is anything that’s been missed. 

Thanks to magentomagik for the headstart…

SET FOREIGN_KEY_CHECKS=0;
 
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_quote`;
TRUNCATE `#_log_url`;
TRUNCATE `#_log_url_info`;
TRUNCATE `#_log_visitor`;
TRUNCATE `#_log_visitor_info`;
TRUNCATE `#_report_event`;
TRUNCATE `#_report_viewed_product_index`;
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 `#_sendfriend_log`;
TRUNCATE `#_tag`;
TRUNCATE `#_tag_relation`;
TRUNCATE `#_tag_summary`;
TRUNCATE `#_tag_properties`;            ## CHECK ME
TRUNCATE `#_wishlist`;
### ??? TRUNCATE `#_log_summary` 

ALTER TABLE `#_customer_address_entityAUTO_INCREMENT=1;
ALTER TABLE `#_customer_address_entity_datetimeAUTO_INCREMENT=1;
ALTER TABLE `#_customer_address_entity_decimalAUTO_INCREMENT=1;
ALTER TABLE `#_customer_address_entity_intAUTO_INCREMENT=1;
ALTER TABLE `#_customer_address_entity_textAUTO_INCREMENT=1;
ALTER TABLE `#_customer_address_entity_varcharAUTO_INCREMENT=1;
ALTER TABLE `#_customer_entityAUTO_INCREMENT=1;
ALTER TABLE `#_customer_entity_datetimeAUTO_INCREMENT=1;
ALTER TABLE `#_customer_entity_decimalAUTO_INCREMENT=1;
ALTER TABLE `#_customer_entity_intAUTO_INCREMENT=1;
ALTER TABLE `#_customer_entity_textAUTO_INCREMENT=1;
ALTER TABLE `#_customer_entity_varcharAUTO_INCREMENT=1;
ALTER TABLE `#_log_customerAUTO_INCREMENT=1;
ALTER TABLE `#_log_quoteAUTO_INCREMENT=1;
ALTER TABLE `#_log_urlAUTO_INCREMENT=1;
ALTER TABLE `#_log_url_infoAUTO_INCREMENT=1;
ALTER TABLE `#_log_visitorAUTO_INCREMENT=1;
ALTER TABLE `#_log_visitor_infoAUTO_INCREMENT=1
ALTER TABLE `#_report_eventAUTO_INCREMENT=1;
ALTER TABLE `#_report_viewed_product_indexAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_orderAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_order_addressAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_order_gridAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_order_itemAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_order_paymentAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_order_status_historyAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_quoteAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_quote_addressAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_quote_address_itemAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_quote_itemAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_quote_item_optionAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_quote_paymentAUTO_INCREMENT=1;
ALTER TABLE `#_sales_flat_quote_shipping_rateAUTO_INCREMENT=1;
ALTER TABLE `#_sendfriend_logAUTO_INCREMENT=1;
ALTER TABLE `#_tagAUTO_INCREMENT=1;
ALTER TABLE `#_tag_relationAUTO_INCREMENT=1;
ALTER TABLE `#_tag_summaryAUTO_INCREMENT=1;
ALTER TABLE `#_tag_propertiesAUTO_INCREMENT=1;
ALTER TABLE `#_wishlistAUTO_INCREMENT=1;

#-- Now, lets Reset all ID counters
TRUNCATE `#_eav_entity_store`;
ALTER TABLE  `#_eav_entity_storeAUTO_INCREMENT=1;
 
SET FOREIGN_KEY_CHECKS=1;

Hope this saves someone some time.  Hope to have the version for individual orders and customers soon…

-HKS

 
Magento Community Magento Community
Magento Community
Magento Community
 
VortexCommerce
Sr. Member
 
Avatar
Total Posts:  104
Joined:  2008-08-06
Harrogate, UK
 

Awesome dude, thanks! Bookmarked for later =]

 
Magento Community Magento Community
Magento Community
Magento Community
 
sahuspilwal
Member
 
Avatar
Total Posts:  37
Joined:  2009-08-02
Hastings, United Kingdom
 

Hi Harikaram

Thanks for the SQL Script. Seems to work quite well for v.1.4.1.1 but for others please note if you have invoiced or shipped any test orders then you will need to add the following tables to the list required for truncation and auto increment otherwise you will find errors on the Admin > Sales > Invoices & Shipments admin pages. The MySQL tables are as follows;

TRUNCATE `sales_flat_invoice_comment`;
TRUNCATE `sales_flat_invoice_grid`;
TRUNCATE `sales_flat_invoice_item`;
TRUNCATE `sales_flat_shipment`;
TRUNCATE `sales_flat_shipment_comment`;
TRUNCATE `sales_flat_shipment_grid`;
TRUNCATE `sales_flat_shipment_item`;
TRUNCATE `sales_flat_shipment_track`;

ALTER TABLE `sales_flat_invoice_commentAUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_gridAUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_itemAUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipmentAUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_commentAUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_gridAUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_itemAUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_trackAUTO_INCREMENT=1;

Other tables maybe required for other purposes such as deleting credit memo’s etc…

Cheers, Sahus

 
Magento Community Magento Community
Magento Community
Magento Community
 
harikaram
Member
 
Avatar
Total Posts:  54
Joined:  2008-07-27
 

Thanks @sahuspilwal.  I just discovered half of this, but the shipment stuff I’d have totally missed…

Maybe I’ll start a wiki page for it…

 
Magento Community Magento Community
Magento Community
Magento Community
 
harikaram
Member
 
Avatar
Total Posts:  54
Joined:  2008-07-27
 

Also DNF:

#_sales_flat_quote

And what about…

#_sales_invoiced_aggregated
#_sales_invoiced_aggregated_order

?

 
Magento Community Magento Community
Magento Community
Magento Community
 
harikaram
Member
 
Avatar
Total Posts:  54
Joined:  2008-07-27
 

Wiki:
http://www.magentocommerce.com/wiki/import-export_and_data_manipulation/deleting_order_and_customer_data_with_sql

 
Magento Community Magento Community
Magento Community
Magento Community
 
harikaram
Member
 
Avatar
Total Posts:  54
Joined:  2008-07-27
 

Deleting a Single Order added to the wiki.

 
Magento Community Magento Community
Magento Community
Magento Community
 
harikaram
Member
 
Avatar
Total Posts:  54
Joined:  2008-07-27
 

Downloadable products added thx to @minzel!

 
Magento Community Magento Community
Magento Community
Magento Community
 
welpix
Jr. Member
 
Avatar
Total Posts:  16
Joined:  2009-03-29
toronto
 

thanks

 
Magento Community Magento Community
Magento Community
Magento Community
 
welpix
Jr. Member
 
Avatar
Total Posts:  16
Joined:  2009-03-29
toronto
 

you are my hero men. I almost google my self out for this query.

thank you

 
Magento Community Magento Community
Magento Community
Magento Community
 
Mark Robinson
Member
 
Total Posts:  40
Joined:  2008-04-08
 

Anyone have the SQL for deleting a single order for CE 1.4.0.1 ? Seems to have different db structure so I’m not confident that the new code would work.

 
Magento Community Magento Community
Magento Community
Magento Community
 
karpik
Jr. Member
 
Total Posts:  15
Joined:  2008-08-06
Wakefield
 

This is the result I get:

Error
SQL query:

################################################## # IMPORTANT INFO ################################################## # Assumes unique order ids across all stores # Does not revert product stock # Search and replace #_ first # UNRESOLVED QUESTIONS… # Is order_id in `*_grid` tables the entity_id from `*_order` or `*_order_grid` ?? # *_grid tables have invoice_id and order_id. Why both? # Is quote-order always 1 to 1? ################################################## # GATHER REQUIRED IDs ################################################## SET @orderId = (SELECT entity_id FROM #_sales_flat_order WHERE increment_id=@incId); SET @quoteId = (SELECT quote_id FROM #_sales_flat_order WHERE entity_id=@orderId); SET @customerId = (SELECT customer_id FROM #_sales_flat_order WHERE entity_id=@orderId); ################################################## # DELETE THE ORDER AND ALL RELATED DATA ################[...]

MySQL said:

#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 ‘SET @quoteId = (SELECT quote_id FROM #_sales_flat_order WHERE’ at line 18

 
Magento Community Magento Community
Magento Community
Magento Community
 
harikaram
Member
 
Avatar
Total Posts:  54
Joined:  2008-07-27
 

Try removing the comments....

Other than that, what MySQL version are you using?  What Magento version?

 
Magento Community Magento Community
Magento Community
Magento Community
 
karpik
Jr. Member
 
Total Posts:  15
Joined:  2008-08-06
Wakefield
 

Hi,

I did remove comments, my Magento version is:  1.4.1.1,
MySQL - 5.0.91-community.

Now I get an error:

Error
SQL query:

DELETE FROM `sales_flat_order_address` WHERE parent_id = @orderId DELETE FROM `sales_flat_order_item` WHERE order_id = @orderId ;

MySQL said:

#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 ‘DELETE FROM `sales_flat_order_item` WHERE order_id=@orderId’ at line 2

Order disappeard from dashboard totals but still can be seen in Sales - Orders.

 
Magento Community Magento Community
Magento Community
Magento Community
 
harikaram
Member
 
Avatar
Total Posts:  54
Joined:  2008-07-27
 

Try executing just these 4 lines:

SET @incId = ‘100000001’;
SET @orderId = (SELECT entity_id FROM #_sales_flat_order WHERE increment_id=@incId);
SET @quoteId = (SELECT quote_id FROM #_sales_flat_order WHERE entity_id=@orderId);
SET @customerId = (SELECT customer_id FROM #_sales_flat_order WHERE entity_id=@orderId);

And tell me what you get…

 
Magento Community Magento Community
Magento Community
Magento Community
 
karpik
Jr. Member
 
Total Posts:  15
Joined:  2008-08-06
Wakefield
 

Hi, I didnt change DB prefix, after I did I get this error :

Now I get an error:

Error
SQL query:

DELETE FROM `sales_flat_order_address` WHERE parent_id = @orderId DELETE FROM `sales_flat_order_item` WHERE order_id = @orderId ;

MySQL said:

#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 ‘DELETE FROM `sales_flat_order_item` WHERE order_id=@orderId’ at line 2

Order disappeard from dashboard totals but still can be seen in Sales - Orders.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 2