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.
Are there are any other tables I have missed?
Thanks in advance.