Deleting specific orders and related data via SQL

Question

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.

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_';

Answer 2

Try this SQL query:

DELETE FROM sales_order WHERE increment_id = '20000354_';
DELETE FROM sales_order_item WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_');
DELETE FROM sales_order_address WHERE parent_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_');
DELETE FROM sales_order_payment WHERE parent_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_');
DELETE FROM sales_order_status_history WHERE parent_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_');
DELETE FROM sales_order_grid WHERE increment_id = '20000354_';
DELETE FROM sales_invoice WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_');
DELETE FROM sales_invoice_item WHERE parent_id = (SELECT entity_id FROM sales_invoice WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_'));
DELETE FROM sales_invoice_grid WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_');
DELETE FROM sales_shipment WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_');
DELETE FROM sales_shipment_item WHERE parent_id = (SELECT entity_id FROM sales_shipment WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_'));
DELETE FROM sales_shipment_item WHERE parent_id = (SELECT entity_id FROM sales_shipment WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_'));
DELETE FROM sales_shipment_grid WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_');
DELETE FROM sales_creditmemo WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_');
DELETE FROM sales_creditmemo_item WHERE parent_id = (SELECT entity_id FROM sales_creditmemo WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_'));
DELETE FROM sales_creditmemo_grid WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '20000354_');

This will delete everything related to order 20000354_.