1

I need to delete about 7500 orders and keep the orders from 26.08.2016 and onward. I would like to do it through db query. Is it possible or I have to stick with admin panel?

3

you can try this, but backup first in case I'm wrong.
I don't recommend using direct queries because you might end up with zombie data.
You can create a script:

First tell the script that you are on the admin area:

Mage::register('isSecureArea', true);

Then get the orders:

$orders = Mage::getModel('sales/order')->getCollection()
     ->addFieldToFilter('created_at', array('lt' => '2016-08-26'));  

Now for each order you have to delete the invoices, invoice items, shipments, shipment items, creditnotes, creditnote items, order items, payment references, quote, quote items and in the end the order itself.

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();
}
  • Marius I believe in your knowledge, considering all 36 tables related to a single orders, why do you omit some of tables from your script? – Zinat Sep 1 '16 at 8:44
  • @Zinat What did I miss? I don't claim it's a perfect script. I can improve it if needed. – Marius Sep 1 '16 at 8:47
  • Ah...I see now. I missed the addresses. I'll fix it in a few minutes. – Marius Sep 1 '16 at 8:50
  • Consider the list of tables in stackoverflow.com/questions/4526914/…, urfusion's solution. – Zinat Sep 1 '16 at 8:51
  • 1
    For some of the tables, the data will be deleted automatically because of the foreign keys and cascades. For example when you delete a quote item the corresponding records from sales_flat_quote_item_option will be deleted. – Marius Sep 1 '16 at 8:53
1

You can use Delete order Extension to delete the order. Just select the order between dates, filter it. Selected all that order and select the action from select box to delete order and submit.

Extension :: https://www.magentocommerce.com/magento-connect/delete-orders-6.html

  • Actually I'm looking for db query, if possible. – Zinat Sep 1 '16 at 8:34
  • If you go with direct query then it may possible you will get some issue after some time because there are lots of relation in DB. – Ashish Jagnani Sep 1 '16 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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