WarpConduit Computing

  • Quick Tips
  • Web Design & Development
  • Graphic Design
  • Home
  • WordPress Plugins
  • Password Generator
  • About

Deleting Individual Orders from Magento

January 3, 2011 by Josh Hartman

Please follow directions according to your version of Magento. Backup your database before running these SQL commands. You are responsible for backing up your database before running SQL commands on it. Run the SQL commands from a MySQL GUI Client, web client such as phpMyAdmin, or command-line interface.

Magento 1.4.x

Varien made deleting orders very simple in 1.4 by linking all the related tables together and setting up cascade deletion. Thank you Varien! Now if you would just let us delete orders through the Admin web interface.

SET @orderId = xxxxxxxxx; #replace this WITH your ORDER NUMBER
 
SET FOREIGN_KEY_CHECKS = 1;
 
DELETE FROM sales_flat_quote
      WHERE reserved_order_id = @orderId;
 
DELETE FROM sales_flat_order
      WHERE increment_id = @orderId;

SET @orderId = xxxxxxxxx; #replace this with your order numberSET FOREIGN_KEY_CHECKS = 1;DELETE FROM sales_flat_quote WHERE reserved_order_id = @orderId;DELETE FROM sales_flat_order WHERE increment_id = @orderId;

Magento 1.3.x

SET @orderId = xxxxxxxxx; #replace this WITH your ORDER NUMBER
 
SET FOREIGN_KEY_CHECKS = 1;
 
SET @salesFlatQuoteId = (
    SELECT entity_id
      FROM sales_flat_quote
     WHERE reserved_order_id = @orderId
);
SET @salesOrderId = (
    SELECT entity_id
      FROM sales_order
     WHERE increment_id = @orderId
);
 
/* temp table used as an array */
CREATE TEMPORARY TABLE del_sales(
    id      INT AUTO_INCREMENT PRIMARY KEY,
    salesId INT(10)
);
 
/* temp table used as an array */
CREATE TEMPORARY TABLE del_statusSales(
    id      INT AUTO_INCREMENT PRIMARY KEY,
    salesId INT(10)
);
 
INSERT INTO del_statusSales (salesId)
    SELECT entity_id
      FROM sales_order_entity_int
     WHERE VALUE = @salesOrderId
       AND attribute_id = ANY (
        SELECT attribute_id
          FROM eav_attribute
         WHERE attribute_code = 'order_id'
    )
       AND entity_id = ANY (
        SELECT entity_id
          FROM sales_order_entity
         WHERE entity_type_id = ANY (
            SELECT entity_type_id
              FROM eav_entity_type
             WHERE entity_type_code = 'invoice'
                OR entity_type_code = 'shipment'
                OR entity_type_code = 'creditmemo'
        )
    );
 
INSERT INTO del_sales (salesId)
    SELECT entity_id
      FROM sales_order_entity
     WHERE parent_id = ANY (
        SELECT salesId
          FROM del_statusSales
    )
       AND entity_type_id = ANY (
        SELECT entity_type_id
          FROM eav_entity_type
          WHERE entity_type_code = 'invoice_item'
             OR entity_type_code = 'invoice_comment'
             OR entity_type_code = 'shipment_item'
             OR entity_type_code = 'shipment_comment'
             OR entity_type_code = 'shipment_track'
             OR entity_type_code = 'creditmemo_item'
             OR entity_type_code = 'creditmemo_comment'
    );
INSERT INTO del_sales (salesId)
    SELECT salesId
      FROM del_statusSales;
 
INSERT INTO del_sales (salesId)
    SELECT entity_id
      FROM sales_order_entity
     WHERE parent_id = @salesOrderId;
 
DELETE FROM sales_order_entity
      WHERE entity_id = ANY (
    SELECT salesId
      FROM del_sales
);
 
DELETE FROM sales_flat_quote
      WHERE reserved_order_id = @orderId;
 
DELETE FROM sales_flat_order_item
      WHERE quote_item_id = @salesFlatQuoteId;
 
DELETE FROM sales_order
      WHERE increment_id = @orderId;
 
/* drop temp tables */
DROP TEMPORARY TABLE del_sales;
DROP TEMPORARY TABLE del_statusSales;

SET @orderId = xxxxxxxxx; #replace this with your order numberSET FOREIGN_KEY_CHECKS = 1;SET @salesFlatQuoteId = ( SELECT entity_id FROM sales_flat_quote WHERE reserved_order_id = @orderId ); SET @salesOrderId = ( SELECT entity_id FROM sales_order WHERE increment_id = @orderId );/* temp table used as an array */ CREATE TEMPORARY TABLE del_sales( id INT AUTO_INCREMENT PRIMARY KEY, salesId INT(10) );/* temp table used as an array */ CREATE TEMPORARY TABLE del_statusSales( id INT AUTO_INCREMENT PRIMARY KEY, salesId INT(10) ); INSERT INTO del_statusSales (salesId) SELECT entity_id FROM sales_order_entity_int WHERE value = @salesOrderId AND attribute_id = ANY ( SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'order_id' ) AND entity_id = ANY ( SELECT entity_id FROM sales_order_entity WHERE entity_type_id = ANY ( SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'invoice' OR entity_type_code = 'shipment' OR entity_type_code = 'creditmemo' ) );INSERT INTO del_sales (salesId) SELECT entity_id FROM sales_order_entity WHERE parent_id = ANY ( SELECT salesId FROM del_statusSales ) AND entity_type_id = ANY ( SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'invoice_item' OR entity_type_code = 'invoice_comment' OR entity_type_code = 'shipment_item' OR entity_type_code = 'shipment_comment' OR entity_type_code = 'shipment_track' OR entity_type_code = 'creditmemo_item' OR entity_type_code = 'creditmemo_comment' ); INSERT INTO del_sales (salesId) SELECT salesId FROM del_statusSales;INSERT INTO del_sales (salesId) SELECT entity_id FROM sales_order_entity WHERE parent_id = @salesOrderId;DELETE FROM sales_order_entity WHERE entity_id = ANY ( SELECT salesId FROM del_sales );DELETE FROM sales_flat_quote WHERE reserved_order_id = @orderId; DELETE FROM sales_flat_order_item WHERE quote_item_id = @salesFlatQuoteId; DELETE FROM sales_order WHERE increment_id = @orderId;/* drop temp tables */ DROP TEMPORARY TABLE del_sales; DROP TEMPORARY TABLE del_statusSales;

Filed Under: Web Design & Development Tagged With: delete, magento, orders

Comments

  1. Ravi says

    April 6, 2011 at 7:42 AM

    Very Good Article.

    Thanks
    BharatMatrimony

  2. Ryan Powszok says

    November 21, 2012 at 10:58 PM

    For Magento v.1.7x

    SET @orderId = xxxxxxxxx; #replace this WITH your ORDER NUMBER
    SET FOREIGN_KEY_CHECKS = 1;
    DELETE FROM sales_flat_quote
          WHERE reserved_order_id = @orderId;
    DELETE FROM sales_flat_order
          WHERE increment_id = @orderId;
    DELETE FROM sales_flat_order_grid
          WHERE increment_id = @orderId;

    SET @orderId = xxxxxxxxx; #replace this WITH your ORDER NUMBER SET FOREIGN_KEY_CHECKS = 1; DELETE FROM sales_flat_quote WHERE reserved_order_id = @orderId; DELETE FROM sales_flat_order WHERE increment_id = @orderId; DELETE FROM sales_flat_order_grid WHERE increment_id = @orderId;

    • Josh Hartman says

      November 22, 2012 at 1:55 AM

      Thanks for the updated info Ryan!

  3. Karthik says

    December 1, 2012 at 12:20 AM

    Got code for 1.7.x, Thank you Ryan!

Connect

  • Facebook
  • GitHub
  • RSS
  • Twitter
  • YouTube

Recent Posts

  • Preserve your MySQL Databases Between Destroy and Up with Vagrant + PuPHPet on Windows
  • Configuring Exim for Authenticated SMTP Relay to Gmail on CentOS/RHEL
  • Adding Custom Fonts to the Beaver Builder Child Theme Customizer

Tags

automatic base64 benchmark cache counter css deflate email font gzip htaccess html image inarray increment inline images in_array javascript jquery link list magento mailto menu metadot mysql number obfuscation opencart operating system order php random redirect rewriterule slashes software timestamp ubuntu unix upgrade url windows windows 7 wordpress

Blogroll

  • CodeIgniter
  • Fusion Forward
  • jQuery
  • Nettuts+
  • Smashing Magazine

© 2019 WarpConduit Computing. All Rights Reserved.