Delete Order - Permanently delete useless Orders, payments, shipments and credit memos

How To Delete Order in Magento [Magento Tips]

For a product to be displayed on your site, it is vital to do testing all the processes related to the product in order to make sure that all things are well-prepared. Unfortunately, it is impossible for a native Magento to remove the orders because Magento itself does not equip this function. As a consequence, a number of test orders remain in your site without removal, which is so inconvenient. Thus, do you have any idea about how to deal with this situation? In this blog, I would like to give you some solutions to removing a test order in your site.


As I have said, Magento does not allows admins to delete orders in your system, despite great efforts, all you can do in a native Magento is to set their status into “Cancelled”. In order to remove unwanted orders, you can follow the suggestions below.

Solution 1: Tackle the problem right in Database

By directly making some changes in Database, you can delete a selected test order or even the whole test orders from your site.

1.1. Delete order one by one

To do so, firstly you have to log on your PhpmyAdmin (use full access). Here, you have to run some queries like the following:
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;

Please remember to replace “order ID” I the above example with the ID of order you wish to delete. Similarly to “prefix”, you can replace it with the one you have selected in your store. Besides, you also need to pay attention to REST of Database.

1.2. Delete all orders

Firstly I have to alert you to be cautious with this mode because it will delete all the orders without differentiating between test orders with actual ones. So if you, specifically owners of new stores, are considering using this way, please make sure all orders are your unwanted and no actual orders are in your store and I suggest that you should take a thorough backup before doing this.

Now, here are the steps for you to follow:

Step 1: Log on PhpMyAdmin
Step 2: Run SQL Query

Here are the queries you need to add in your database to delete all orders:

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;

When finished, all orders are deleted in your site!

Solution 2: Write script

By creating a file with the level as exactly as index.php, you can quickly delete the unnecessary with its order ID.

load($orderId);

$invoices = $order->getInvoiceCollection();
foreach ($invoices as $invoice){
$invoice->delete();
}

$creditnotes = $order->getCreditmemosCollection();
foreach ($creditnotes as $creditnote){
$creditnote->delete();
}

$shipments = $order->getShipmentsCollection();
foreach ($shipments as $shipment){
$shipment->delete();
}

$order->delete();
?>

Now, the order is removed entirely from your system.

Personally speaking, between the two above ways, the latter appears to be more or less better as it does not directly interfere with the database. Nonetheless, there is a very convenient and economical way for you to remove unwanted test orders, which hinges on the extension called “Magento Delete Order extension”, for instance, that of BSS Commerce.

Solution 3: Use a Magento Delete Order extension

This solution does not require doers much technical knowledge and you can avoid the direct intervention with the database which may cause harms. Once a Magento Delete Order extension is equipped in your store, you can not only delete your unwanted orders but also remove unnecessary invoices, shipments or Credit memos within clicks. After running the extension, you only need to go to the admin panel, choose Sale section and the unwanted orders, unnecessary invoices, shipments or Credit memos to delete from the system. (A new version of Delete Order extension for Magento 2 is available now)

The above are some ways to delete unwanted orders from your store. Among these, hopefully, you can find the best one for your online store.

One thought on “How To Delete Order in Magento [Magento Tips]”

  • Alex

    Great read. It's annoying that default magento does not allow delete orders. As I'm a low-tech man I decided to choose using the extension as suggested in the article. It sounds a smart choice to me :) Thanks for sharing.

    Reply
Leave a Reply