magento Archives - WarpConduit Computing

Magento Order Numbers Sometimes the need arises where you must change the Magento order numbering. Perhaps it is necessary to avoid conflict with a separate Magento installation or other ecommerce platform. Or maybe you just want to increase your order number to look like you’ve processed hundreds of thousands of orders. Whatever the case may be here are some SQL commands you can run to check the status of your store’s order numbers, and commands to also change the numbering. In addition, I’ve included SQL commands to modify the invoice, shipment, and credit memo increment IDs as well.

Note: These queries were tested on Magento Community Edition v1.6.2.0. I fully expect the increment ID queries to work on v1.4.1 and above, but DO NOT set the increment prefix to NULL on anything less than v1.6.0. Backup your database before making any changes in case your copy of Magento reacts unexpectedly.

Update 12/6/2017: This method has been successfully tested on Magento CE v1.9.3.6.

Find the Current Increment IDs for All Stores

SELECT core_store_group.name as group_name, core_website.name as website_name, core_store.name as store_name, core_store.store_id, increment_prefix, increment_last_id, entity_type_code
FROM eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
INNER JOIN core_store ON core_store.store_id = eav_entity_store.store_id
INNER JOIN core_store_group ON core_store_group.group_id = core_store.group_id
INNER JOIN core_website ON core_website.website_id = core_store.website_id
WHERE eav_entity_store.store_id != 0 ORDER BY eav_entity_store.store_id;

This will display your current increment ID and prefix for all document types (quotes, orders, invoices, shipments, and credit memos). In addition it will show you the website group, website name, store name, and store ID for each type of increment ID to help you in updating a specific store among multiple stores.
When making changes keep in mind that the increment_prefix field type is varchar(20) and that the increment_last_id field type is varchar(50).

Order Increment ID and Prefix

Change your Order Increment ID on All Stores

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_last_id='XXXXXXXXXX'
WHERE eav_entity_type.entity_type_code='order';

Replace the X‘s with your desired order number and run the query.

Change your Order Prefix on All Stores

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_prefix='X'
WHERE eav_entity_type.entity_type_code='order';

Replace the X with your desired order prefix or remove the quotes and set X to NULL (no quotes) to disable the order prefix, then run the query.

Change your Order Increment ID on a Specific Store

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_last_id='XXXXXXXXXX'
WHERE eav_entity_type.entity_type_code='order' AND eav_entity_store.store_id = 'Y';

Replace the X‘s with your desired order number, replace Y with the store ID of the store you want to modify, then run the query.

Change your Order Prefix on a Specific Store

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_prefix='X'
WHERE eav_entity_type.entity_type_code='order' AND eav_entity_store.store_id = 'Y';

Replace the X with your desired order prefix or remove the quotes and set X to NULL (no quotes) to disable the order prefix, then replace Y with the store ID of the store you want to modify. Run the query.

Invoice Increment ID and Prefix

Change your Invoice Increment ID on All Stores

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_last_id='XXXXXXXXXX'
WHERE eav_entity_type.entity_type_code='invoice';

Replace the X‘s with your desired invoice number and run the query.

Change your Invoice Prefix on All Stores

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_prefix='X'
WHERE eav_entity_type.entity_type_code='invoice';

Replace the X with your desired invoice prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then run the query.

Change your Invoice Increment ID on a Specific Store

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_last_id='XXXXXXXXXX'
WHERE eav_entity_type.entity_type_code='invoice' AND eav_entity_store.store_id = 'Y';

Replace the X‘s with your desired invoice number, replace Y with the store ID of the store you want to modify, then run the query.

Change your Invoice Prefix on a Specific Store

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_prefix='X'
WHERE eav_entity_type.entity_type_code='invoice' AND eav_entity_store.store_id = 'Y';

Replace the X with your desired invoice prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then replace Y with the store ID of the store you want to modify. Run the query.

Shipment Increment ID and Prefix

Change your Shipment Increment ID on All Stores

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_last_id='XXXXXXXXXX'
WHERE eav_entity_type.entity_type_code='shipment';

Replace the X‘s with your desired shipment number and run the query.

Change your Shipment Prefix on All Stores

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_prefix='X'
WHERE eav_entity_type.entity_type_code='shipment';

Replace the X with your desired shipment prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then run the query.

Change your Shipment Increment ID on a Specific Store

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_last_id='XXXXXXXXXX'
WHERE eav_entity_type.entity_type_code='shipment' AND eav_entity_store.store_id = 'Y';

Replace the X‘s with your desired shipment number, replace Y with the store ID of the store you want to modify, then run the query.

Change your Shipment Prefix on a Specific Store

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_prefix='X'
WHERE eav_entity_type.entity_type_code='shipment' AND eav_entity_store.store_id = 'Y';

Replace the X with your desired shipment prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then replace Y with the store ID of the store you want to modify. Run the query.

Credit Memo Increment ID and Prefix

Change your Credit Memo Increment ID on All Stores

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_last_id='XXXXXXXXXX'
WHERE eav_entity_type.entity_type_code='creditmemo';

Replace the X‘s with your desired credit memo number and run the query.

Change your Credit Memo Prefix on All Stores

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_prefix='X'
WHERE eav_entity_type.entity_type_code='creditmemo';

Replace the X with your desired credit memo prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then run the query.

Change your Credit Memo Increment ID on a Specific Store

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_last_id='XXXXXXXXXX'
WHERE eav_entity_type.entity_type_code='creditmemo' AND eav_entity_store.store_id = 'Y';

Replace the X‘s with your desired credit memo number, replace Y with the store ID of the store you want to modify, then run the query.

Change your Credit Memo Prefix on a Specific Store

UPDATE eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET eav_entity_store.increment_prefix='X'
WHERE eav_entity_type.entity_type_code='creditmemo' AND eav_entity_store.store_id = 'Y';

Replace the X with your desired credit memo prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then replace Y with the store ID of the store you want to modify. Run the query.

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;

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;

How to Change the Order Increment ID and Prefix in Magento