How to remove test orders from Magento2

Question

Please explain me the proper way to remove the test orders in Magento2 website.I removed all records from 'sales_order' table but still the orders exist in the backend.

Best and simple to use the extension, https://magecomp.com/magento-2-delete-orders.html — Gaurav Jain, Mar 09 '18 at 06:33
Yes, I agree and I prefer to use a better delete orders extension https://github.com/mageplaza/magento-2-delete-orders — Say Truth, May 09 '18 at 04:44

score 55 · Accepted Answer · answered Sep 09 '16 at 15:41

Tested in Magento 2.1.0

Be safe : make a backup of your sql first.

Use the sql below according to your needs :

SET FOREIGN_KEY_CHECKS=0;

# Clean order history
TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;
TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;
TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;

# Clean order infos
TRUNCATE TABLE `sales_creditmemo`;
TRUNCATE TABLE `sales_creditmemo_comment`;
TRUNCATE TABLE `sales_creditmemo_grid`;
TRUNCATE TABLE `sales_creditmemo_item`;
TRUNCATE TABLE `sales_invoice`;
TRUNCATE TABLE `sales_invoiced_aggregated`;
TRUNCATE TABLE `sales_invoiced_aggregated_order`;
TRUNCATE TABLE `sales_invoice_comment`;
TRUNCATE TABLE `sales_invoice_grid`;
TRUNCATE TABLE `sales_invoice_item`;
TRUNCATE TABLE `sales_order`;
TRUNCATE TABLE `sales_order_address`;
TRUNCATE TABLE `sales_order_aggregated_created`;
TRUNCATE TABLE `sales_order_aggregated_updated`;
TRUNCATE TABLE `sales_order_grid`;
TRUNCATE TABLE `sales_order_item`;
TRUNCATE TABLE `sales_order_payment`;
TRUNCATE TABLE `sales_order_status_history`;
TRUNCATE TABLE `sales_order_tax`;
TRUNCATE TABLE `sales_order_tax_item`;
TRUNCATE TABLE `sales_payment_transaction`;
TRUNCATE TABLE `sales_refunded_aggregated`;
TRUNCATE TABLE `sales_refunded_aggregated_order`;
TRUNCATE TABLE `sales_shipment`;
TRUNCATE TABLE `sales_shipment_comment`;
TRUNCATE TABLE `sales_shipment_grid`;
TRUNCATE TABLE `sales_shipment_item`;
TRUNCATE TABLE `sales_shipment_track`;
TRUNCATE TABLE `sales_shipping_aggregated`;
TRUNCATE TABLE `sales_shipping_aggregated_order`;

# Clean cart infos
TRUNCATE TABLE `quote`;
TRUNCATE TABLE `quote_address`;
TRUNCATE TABLE `quote_address_item`;
TRUNCATE TABLE `quote_id_mask`;
TRUNCATE TABLE `quote_item`;
TRUNCATE TABLE `quote_item_option`;
TRUNCATE TABLE `quote_payment`;
TRUNCATE TABLE `quote_shipping_rate`;

# Reset indexes (if you want your orders number start back to 1
TRUNCATE TABLE sequence_invoice_1;
TRUNCATE TABLE sequence_order_1;
TRUNCATE TABLE sequence_shipment_1;
TRUNCATE TABLE sequence_creditmemo_1;


SET FOREIGN_KEY_CHECKS=1;

DO NOT TRUNCATE / EMPTY THE FOLLOWING :

sales_order_status
sales_sequence_meta
sales_sequence_profile
sales_order_status_label
sales_order_status_state

Would this answer be more comprehensive than the selected one at this point? — frostshoxx, Oct 02 '16 at 15:57
This script didn't work for me (magento2 CE 2.2.1). TRUNCATE TABLE `sales_creditmemo` MySQL said: Documentation #1701 - Cannot truncate a table referenced in a foreign key constraint (`fabtablabtest_preinstalled_magento`.`sales_creditmemo_comment`, CONSTRAINT `SALES_CREDITMEMO_COMMENT_PARENT_ID_SALES_CREDITMEMO_ENTITY_ID` FOREIGN KEY (`parent_id`) REFERENCES `fabtablabtest_) — Michel Tol, Nov 20 '17 at 11:29
@MichelTol If you're using phpMyAdmin, you have to uncheck the "Enable foreign key checks" box below the textarea where you paste the SQL. — skilar, Jul 25 '18 at 05:07
caution ! if you are using more than 1 stores, you should also truncate sequence_invoice_2...X tables — roman204, Oct 03 '18 at 09:50

score 4 · Answer 2 · answered Apr 29 '19 at 15:57

This also works in Magento 2.3.1 . In addition to user1171440's answer, I'd also mention the possibility to reset / truncate test customer data:

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE `customer_address_entity`;
TRUNCATE TABLE `customer_address_entity_datetime`;
TRUNCATE TABLE `customer_address_entity_decimal`;
TRUNCATE TABLE `customer_address_entity_int`;
TRUNCATE TABLE `customer_address_entity_text`;
TRUNCATE TABLE `customer_address_entity_varchar`;
TRUNCATE TABLE `customer_entity`;
TRUNCATE TABLE `customer_entity_datetime`;
TRUNCATE TABLE `customer_entity_decimal`;
TRUNCATE TABLE `customer_entity_int`;
TRUNCATE TABLE `customer_entity_text`;
TRUNCATE TABLE `customer_entity_varchar`;
TRUNCATE TABLE `customer_grid_flat`;
TRUNCATE TABLE `customer_visitor`;
SET FOREIGN_KEY_CHECKS=1;

Vikas Dobariya · Answer 3 · 2021-09-16T19:16:26.143

You can delete order via programmatically:

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();

$order = $objectManager->create('Magento\Sales\Model\Order')->getCollection()
     
->addFieldToFilter('entity_id', (array) $orderIds);

foreach ($orders as $o) {

//load order object - I know it's not ok to use load in a loop but it 

should be ok since it's a one time script

$order = $objectManager->create('Magento\Sales\Model\Order')->load($o->getId());

//delete all order items

$items = $order->getAllItems();

foreach ($items as $item) {

    $item->delete();

}

$invoices = $order->getInvoiceCollection();

foreach ($invoices as $invoice){

//delete all invoice items

    $items = $invoice->getAllItems();

    foreach ($items as $item) {

        $item->delete();

    }

    //delete invoice
    $invoice->delete();
}

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

  //delete all creditnote items

    $items = $creditnote->getAllItems();
    foreach ($items as $item) {
        $item->delete();
    }
    //delete credit note
    $creditnote->delete();
}
$shipments = $order->getShipmentsCollection();
foreach ($shipments as $shipment){

  //delete all shipment items

    $items = $shipment->getAllItems();
    foreach ($items as $item) {
        $item->delete();
    }
    //delete shipment
    $shipment->delete();
}

Why is here Magento1 code used? $order = Mage::getModel('sales/order')->load($o->getId()); — MSQ, Mar 12 '19 at 13:07
plus this deletes items, invoices, etc but not the order record from sales_order table. Exception #0 (Magento\Framework\Exception\LocalizedException): Delete operation is forbidden for current area Should we then delete the orders record manually from sales_order table??? please guide. — MSQ, Mar 12 '19 at 13:59

score -1 · Answer 4 · edited Jul 30 '16 at 13:29

-1

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE sales_order  ;
TRUNCATE TABLE sales_order_grid ;
TRUNCATE TABLE sales_invoice ;
TRUNCATE TABLE sales_invoice_grid ;
TRUNCATE TABLE sales_creditmemo ;
TRUNCATE TABLE sales_creditmemo_grid ;
TRUNCATE TABLE sales_shipment ;
TRUNCATE TABLE sales_shipment_grid ;

SET FOREIGN_KEY_CHECKS=1;

edited Jul 30 '16 at 13:29

Ibrahim Lawal

1,168
16
31

answered Jul 14 '16 at 06:41

Kapil Singhathia

255
2
8

2

Running this query can get you in trouble. It is incomplete. I strongly advise against using this query. For example, sales_order_tax_item table is not truncated. This will result in new orders (which will have same ids as old orders) to have several rows in this table (including wrong rows from the old orders). As a consequence, the tax information will be wrong for the new order items. – mimarcel Oct 24 '16 at 13:49

score -1 · Answer 5 · answered May 06 '17 at 06:09

SET FOREIGN_KEY_CHECKS=0;

# Clean order history
TRUNCATE TABLE `mg_sales_bestsellers_aggregated_daily`;
TRUNCATE TABLE `mg_sales_bestsellers_aggregated_monthly`;
TRUNCATE TABLE `mg_sales_bestsellers_aggregated_yearly`;

# Clean order infos
TRUNCATE TABLE `mg_sales_creditmemo`;
TRUNCATE TABLE `mg_sales_creditmemo_comment`;
TRUNCATE TABLE `mg_sales_creditmemo_grid`;
TRUNCATE TABLE `mg_sales_creditmemo_item`;
TRUNCATE TABLE `mg_sales_invoice`;
TRUNCATE TABLE `mg_sales_invoiced_aggregated`;
TRUNCATE TABLE `mg_sales_invoiced_aggregated_order`;
TRUNCATE TABLE `mg_sales_invoice_comment`;
TRUNCATE TABLE `mg_sales_invoice_grid`;
TRUNCATE TABLE `mg_sales_invoice_item`;
TRUNCATE TABLE `mg_sales_order`;
TRUNCATE TABLE `mg_sales_order_address`;
TRUNCATE TABLE `mg_sales_order_aggregated_created`;
TRUNCATE TABLE `mg_sales_order_aggregated_updated`;
TRUNCATE TABLE `mg_sales_order_grid`;
TRUNCATE TABLE `mg_sales_order_item`;
TRUNCATE TABLE `mg_sales_order_payment`;
TRUNCATE TABLE `mg_sales_order_status_history`;
TRUNCATE TABLE `mg_sales_order_tax`;
TRUNCATE TABLE `mg_sales_order_tax_item`;
TRUNCATE TABLE `mg_sales_payment_transaction`;
TRUNCATE TABLE `mg_sales_refunded_aggregated`;
TRUNCATE TABLE `mg_sales_refunded_aggregated_order`;
TRUNCATE TABLE `mg_sales_shipment`;
TRUNCATE TABLE `mg_sales_shipment_comment`;
TRUNCATE TABLE `mg_sales_shipment_grid`;
TRUNCATE TABLE `mg_sales_shipment_item`;
TRUNCATE TABLE `mg_sales_shipment_track`;
TRUNCATE TABLE `mg_sales_shipping_aggregated`;
TRUNCATE TABLE `mg_sales_shipping_aggregated_order`;

# Clean cart infos
TRUNCATE TABLE `mg_quote`;
TRUNCATE TABLE `mg_quote_address`;
TRUNCATE TABLE `mg_quote_address_item`;
TRUNCATE TABLE `mg_quote_id_mask`;
TRUNCATE TABLE `mg_quote_item`;
TRUNCATE TABLE `mg_quote_item_option`;
TRUNCATE TABLE `mg_quote_payment`;
TRUNCATE TABLE `mg_quote_shipping_rate`;

# Reset indexes (if you want your orders number start back to 1
TRUNCATE TABLE mg_sequence_invoice_1;
TRUNCATE TABLE mg_sequence_order_1;
TRUNCATE TABLE mg_sequence_shipment_1;
TRUNCATE TABLE mg_sequence_creditmemo_1;


SET FOREIGN_KEY_CHECKS=1;

score -2 · Answer 6 · answered Jun 17 '16 at 07:08

to delete test Orders from Magento2 run below Mysql Query :

TRUNCATE TABLE sales_order  ;
TRUNCATE TABLE sales_order_grid ;
TRUNCATE TABLE sales_invoice ;
TRUNCATE TABLE sales_invoice_grid ;
TRUNCATE TABLE sales_creditmemo ;
TRUNCATE TABLE sales_creditmemo_grid ;
TRUNCATE TABLE sales_shipment ;
TRUNCATE TABLE sales_shipment_grid ;

How to remove test orders from Magento2

6 Answers6