I am currently looking at deleting old order-related data (including shipment, invoice, creditmemo and payment_transaction) from a Magento installation (currently 2.4.5-p1) and I think I have nailed down a set of SQL DELETE’s that will clear it all safely. 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).
To explain the queries a little better, I am looking up each record using the increment_id from each Order, using the underscare wildcard to represent a single character (the example below should find orders 200003540 to 200003549 inclusive).
SET @increment_id = '20000354_';
DELETE FROM `sales_creditmemo_item` WHERE parent_id IN (SELECT entity_id FROM `sales_creditmemo` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id));
DELETE FROM `sales_creditmemo_grid` WHERE entity_id IN (SELECT entity_id FROM `sales_creditmemo` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id));
DELETE FROM `sales_creditmemo_comment` WHERE parent_id IN (SELECT entity_id FROM `sales_creditmemo` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id));
DELETE FROM `sales_creditmemo` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id);
DELETE FROM `sales_invoice_item` WHERE parent_id IN (SELECT entity_id FROM `sales_invoice` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id));
DELETE FROM `sales_invoice_grid` WHERE entity_id IN (SELECT entity_id FROM `sales_invoice` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id));
DELETE FROM `sales_invoice_comment` WHERE parent_id IN (SELECT entity_id FROM `sales_invoice` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id));
DELETE FROM `sales_invoice` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id);
DELETE FROM `sales_shipment_track` WHERE parent_id IN (SELECT entity_id FROM `sales_shipment` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id));
DELETE FROM `sales_shipment_item` WHERE parent_id IN (SELECT entity_id FROM `sales_shipment` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id));
DELETE FROM `sales_shipment_grid` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id);
DELETE FROM `sales_shipment_comment` WHERE parent_id IN (SELECT entity_id FROM `sales_shipment` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id));
DELETE FROM `sales_shipment` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id);
DELETE FROM `sales_payment_transaction` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id);
DELETE FROM `sales_order_tax_item` WHERE tax_id IN (SELECT tax_id FROM `sales_order_tax` WHERE order_id IN (SELECT entity_id FROM `sales_order` 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_status_history` WHERE parent_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id);
DELETE FROM `sales_order_payment` WHERE parent_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id);
DELETE FROM `sales_order_item` WHERE order_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id);
DELETE FROM `sales_order_grid` WHERE entity_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id);
DELETE FROM `sales_order_address` WHERE parent_id IN (SELECT entity_id FROM `sales_order` WHERE increment_id LIKE @increment_id);
DELETE FROM `sales_order` WHERE entity_id IN (SELECT entity_id 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.
Does anyone know if there are any additional tables I should be looking at?
Thanks in advance.