Magento 2: how to delete orders from the database

Question

How can I delete the test orders created in my store? I have opened the database but I couldn't find the order table. Please help me to delete the orders. I'm using the later version of Magento 2.

Answer 1

You can delete all the orders, order history, shipments, invoices, credit memos, also the quotes, quote items from database by following SQL:

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`;

If tax enable in store

TRUNCATE TABLE `tax_order_aggregated_created`;
TRUNCATE TABLE `tax_order_aggregated_updated`;

#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;

Before doing above take a backup of your database.

Hope this will help.

Also a good overview: https://gist.github.com/sjovanig/3c18d687c60bb39cd752ce63db5d72f7

Answer 2

I suggest you to avoid direct mess with SQL.

You can use any good extension like Mageplaza

Another option is to create a script on root and delete order programmatically

You can create a file at root with following code:

<?php

ini_set('error_reporting', E_ALL);
ini_set("display_errors", "1");

use Magento\Framework\App\Bootstrap;
require 'app/bootstrap.php';

$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();
$registry = $objectManager->get('Magento\Framework\Registry');
$state = $objectManager->get('Magento\Framework\App\State');
$state->setAreaCode('frontend');

$ids = array(1,2,3,4); // your order_id 

foreach ($ids as $id) {

    $order = $objectManager->create('Magento\Sales\Model\Order')->load($id);
    $registry->register('isSecureArea','true');
    $order->delete();
    $registry->unregister('isSecureArea');
    echo "order deleted";

}

Edit I

If you want to delete order using script you can put above code on root folder of magento.After that you can hit URL in browser.

For example your magento is installed at www.example.com and your file name is deleteOrder.php you can run it by:

 www.example.com/deleteOrder.php

If you want to install extension you need to

- Extract folder at [magentoRoot]/app/code
- Open terminal and run cd [magentoRoot] //change to root dir
- php bin/magento setup:upgrade
- php bin/magento cache:flush
- php bin/magento setup:static-content:deploy (only required in production mode)

You can also find document at Official Site

Answer 3

Make sure to remove all invoices,shipments,creditmemos of the order first.

$incrementId = 10000342;
$order       = $objectManager->create('Magento\Sales\Model\Order')->loadByIncrementId($incrementId);
//or
//$order = $objectManager->create('\Magento\Sales\Model\Order')->load($orderId);

$registry = $objectManager->get('Magento\Framework\Registry');
$registry->register('isSecureArea','true');

$invoices    = $order->getInvoiceCollection();
foreach ($invoices as $invoice) {
    $invoice->delete();
}
$shipments = $order->getShipmentsCollection();
foreach ($shipments as $shipment) {
    $shipment->delete();
}
$creditmemos = $order->getCreditmemosCollection();
foreach ($creditmemos as $creditmemo) {
    $creditmemo->delete();
}
// delete invoices/shipments/creditmemos & then finally delete order
$order->delete();
$registry->unregister('isSecureArea');

Answer 4

in case Anyone still interested in this, i did it without installing any extension, steps:

• download n98-magerun2 from github https://github.com/netz98/n98-magerun2

• run this to access dev console : ./n98-magerun2.phar dev:console --area=frontend

• run this script

   $registry = $di->get('Magento\Framework\Registry');
   $startId = 1;
   $endId = 7000;
   for ($i = $startId; $i < $endId; $i++) {
       try {
           $order = $di->create('Magento\Sales\Model\Order')->load($i);
           $registry->register('isSecureArea', 'true');
           $order->delete();
           $registry->unregister('isSecureArea');
           echo "order deleted";
       } catch (\Exception $th) {
           //throw $th;
       }
   }
  

change start and end id