Delete orders by store

Question

I am tidying up old sites from an EE installation (1.12)

I need to clear the orders from the site as well, static blocks and pages have been easy enough to sort with SQL.

I want to do the same to remove the Orders but I have no idea where to start with how the SQL would be created.

I've tried a bunch of plugins but they seem to not want to work with the old EE version we have.

How do I then remove all orders based on a store? The order IDs should all begin with a 4 letter code (say USEN) which is unique for the store.

Answer 1

Try to delete rows from sales_flat_order (please note that you should not truncate, but use delete query)

delete from sales_flat_order

It should also delete child rows in related tables (sales_flat_order_item, sales_flat_invoice, etc..)

try it, it should work.

Answer 2

delete orders by your store_id

$orders = Mage::getModel('sales/order')->getCollection()
 ->addFieldToFilter('store_id', $yourStoreId);

before deleting order you should delete invoice, shipment, creditmemo, Payments and quote and then delete order.

foreach ($orders as $order) {
    //delete invoices
    $invoices = $order->getInvoiceCollection();
    foreach ($invoices as $invoice) {
    //invoice items will be deleted as well
    $invoice->delete();
    }
    //delete invoices
    $shipments = $order->getShipmentsCollection();
    foreach ($shipments as $shipment) {
    //shipment items will be deleted as well
    $shipment->delete();
    }
    //delete credit notes
    $creditMemos = $order->getCreditmemosCollection();
    foreach ($creditMemos as $creditMemo) {
    //credit note items will be deleted as well
    $creditMemo->delete();
    }
    //delete payment references
    $payments = $order->getPaymentsCollection();
    foreach ($payments as $payment) {
    $payment->delete();
    }
    //delete quote
    $quote = Mage::getModel('sales/quote')->load($order->getQuoteId());  
    if ($quote->getId()) {
    foreach ($quote->getAllItems() as $item) {
        $item->delete();
    }
    $quote->delete();
    }
    //delete the order. items will be deleted as well. So does addresses.
    $order->delete();
}

Credit to Marius for this answer Delete all orders older than specific date by db query