I am currently looking at deleting old order-related data (includes order, shipment, invoice, creditmemo and payments) from a Magento installation (currently 2.4.5-p1). I am ignoring the aggregated tables as I assume these are used for the graphs shown in the Magento Admin homepage (I never use the reports or statistics sections), which I want to keep (IE: Orders & Amounts, Today, 24 Hours, 7 Days, Month, YTD, 2YTD).

In the queries below, I look up each row with the increment_id from each sales_order, using the underscore wildcard to represent a single character (the example below should find orders 200003540 to 200003549 inclusive).

I also thank Andrew Noble for his response regarding my previous overkill SQL.

SET @increment_id = '20000354_';
DELETE FROM `sales_creditmemo_grid` WHERE order_increment_id LIKE @increment_id;
DELETE FROM `sales_invoice_grid` WHERE order_increment_id LIKE @increment_id;
DELETE FROM `sales_shipment_grid` WHERE order_increment_id LIKE @increment_id;
DELETE FROM `sales_order_grid` WHERE increment_id LIKE @increment_id;
DELETE FROM `sales_order_tax` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id);
DELETE FROM `sales_order` WHERE increment_id LIKE @increment_id;

Based on my existing tests, all these queries function as expected when I set a single value for increment_id.

The list of tables deleting by cascade when a row from sales_order is deleted follows.

  • sales_creditmemo_item
  • sales_creditmemo_comment
  • sales_creditmemo
  • sales_invoice_item
  • sales_invoice_comment
  • sales_invoice
  • sales_shipment_track
  • sales_shipment_item
  • sales_shipment_comment
  • sales_shipment
  • sales_payment_transaction
  • sales_order_status_history
  • sales_order_payment
  • sales_order_item
  • sales_order_address
  • sales_order_tax_item

Are there are any other tables I have missed?

Thanks in advance.

  • I'm assuming the above post is now correct and am not missing any tables from the deletes. I thank Andrew Noble for his guidance and have accepted his answer.
    – fearfelt
    Jun 5 at 9:58

1 Answer 1


The above SQL seems like overkill. All of the tables you listed have foreign keys to the sales_order table which are configured to cascade on delete.

So if you delete the order from sales_order, the changes will automatically cascade to the other tables and delete any invoices, payments, credit memos etc that are linked to that order.

Just running the below SQL query alone is enough:

DELETE FROM sales_order WHERE increment_id LIKE '20000354_';
  • The records are still shown in sales_order_grid, sales_invoice_grid and sales_creditmemo_grid, so this will explain why they are still shown in the magento admin listings.
    – fearfelt
    Jun 2 at 11:02
  • Further to this, related data in the sales_shipment_grid and sales_order_tax tables are not removed. Everywhere else, the data has been deleted.
    – fearfelt
    Jun 2 at 11:23
  • I'm thinking should I update the SQL in my original question to use your suggestion plus handle the other deletes not executed. The reason being that the question is about whether I missed any tables from the deletes.
    – fearfelt
    Jun 2 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.