Delete Orders

[KiranBhushan]

Hi,

We have our website using Magento 1.9CE, there are a lot of test orders along with the real customer orders. I would like to delete the order manually using sql query. I do know there are extensions available for deleting the orders via admin panel but I would like to know how to delete orders by sql query.

Is deleting the entry using increment_id from sales_flat_order table will also delete all the related data in other tables completely so that there is no reference to that deleted order anywhere?

Regards

Kiran Bhushan

[Damian Culotta]

Hi @KiranBhushan,

Yes but you can check the table structure to see the relations and constraints if you need more information.

[KiranBhushan]

Hi Damian,

I am looking for a more comprehensive tutorial for Magento 1.9 CE database and some custom queries sample to get/delete a customer, order data in one single query. Since after searching through a lot of online forums I am still not able to get any document specifying the database relation and details as well as a tutorial about customizing the admin panel. 

Regards

Kiran Bhushan

[gelanivishal · Accepted solution]

Hello @KiranBhushan,

For delete specific orders, You have to run this query for each orders

set @increment_id='10000001';
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;

and you have to delete all orders then please perform below query

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;
SET FOREIGN_KEY_CHECKS=1;

Let me know if you have any trouble.

--
if issue is solved, Click "Kudos" & "Accept as Solution"

[travis99872]

@gelanivishal any idea how to make this work for magento 2.3? When i run the first command, i get "prefix_sales_order_entity doesn't exist"

[johnpetersdca2]

But, for non-technical people, there is the Magento 2 Admin Order Grid extension. Haven't you tried it already?

It has the delete order feature and many other features for order management, so you don't have to use a separate extension for deleting orders.