How To Delete Magento Test Orders From A Magento Store

delete-magento-test-orders

Are you wondering how to delete all test orders you have made in Magento while testing? In Sandbox mode store owners performs many transactions to make sure that their Magento powered store is capable of handling payment transactions. You can use any Payment Gateways like PayPal, Authorize.net, PayFlow or Amazon Simple Pay. But all the test orders remain in system even when your store goes into production.
Unfortunately, there is no way in Magento to delete those test orders which were made while testing the Magento store. You can only process these order and set their status as “Cancelled” but you can’t really delete those test orders from your store. I am sure that you would love to see a “Delete selected Orders” button in Magento Admin panel; unfortunately you won’t find such functionality. In order to delete these test orders you have to log in to your PhpmyAdmin using full access and run few queries to get rid of those test orders.

How To Delete A Single Test Order Using PhpMyAdmin

You need to run the following query on your database using PhpMyAdmin:

1
2
3
4
  set @increment_id='200000111';
  select @order_id:=entity_id from prefix_sales_order_entity where increment_id=@increment_id;
  delete from prefix_sales_order_entity where entity_id=@order_id or parent_id=@order_id;
  delete from prefix_sales_order where increment_id=@increment_id;

In the above example we are trying to delete test order # ‘200000111’. Generally people use prefix so change the “prefix_” in the above query with the prefix you have chosen for your Magento store.

The above query will delete the single test order or even an actual order from your Magento store. All you have to do is provide an order number and database prefix. Rest will be taken care of automatically.

How To Delete All Magento Test Orders In One Go

This step is done only on new store which are ready forproduction and don’t have any “Actual Orders” in the system. I would highly recommend taking a full backup before you proceed. Basically when you run the following queries on your Magento database it will complete wipe out all the records and reset the order counters. If you are about to indulge, then please make sure that you know what you are doing.

Beware: The following steps can’t be reversed.

Go to your PhpMyAdmin and run SQL Query

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;
 
  -- lets 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;
 
  -- Now, lets Reset all ID counters
  TRUNCATE `eav_entity_store`;
  ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;
 
  SET FOREIGN_KEY_CHECKS=1;

Optional: If you are looking to set appropriate prefixes for orders, invoices, shipments, credit memos then run the following query as well:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
 INSERT INTO  `YOUR_DB_NAME`.`eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id` ,`increment_prefix` ,`increment_last_id`) VALUES ('1',  '11',  '1',  '1',  '000000000');
 update `eav_entity_store` set `increment_prefix`= 1 where `entity_type_id`='4' and `store_id`='1';
 update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='4' and `store_id`='1';
 
 INSERT INTO  `YOUR_DB_NAME`.`eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id` ,`increment_prefix` ,`increment_last_id`) VALUES ('2',  '16',  '1',  '2',  '000000000');
 update `eav_entity_store` set `increment_prefix`= 2 where `entity_type_id`='18' and `store_id`='1';
 update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='18' and `store_id`='1';
 
 INSERT INTO  `YOUR_DB_NAME`.`eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id` ,`increment_prefix` ,`increment_last_id`) VALUES ('3',  '19',  '1',  '3',  '000000000');
 update `eav_entity_store` set `increment_prefix`= 3 where `entity_type_id`='24' and `store_id`='1';
 update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='24' and `store_id`='1';
 
 INSERT INTO  `YOUR_DB_NAME`.`eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id` ,`increment_prefix` ,`increment_last_id`) VALUES ('4',  '23',  '1',  '4',  '000000000');
 update `eav_entity_store` set `increment_prefix`= 4 where `entity_type_id`='28' and `store_id`='1';
 update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='28' and `store_id`='1';

Now, all the test orders, customer orders, logs etc are all gone from your Magento store and it will be in a clean state to start over.

Should you run into any trouble? Please leave me a comment and let me know. Don’t forget to subscribe our RSS to receive latest updates delivered to your mailbox for Free.