This article discusses how to change the increment ID for an Adobe Commerce database (DB) entity (order, invoice, credit memo, etc.) on a particular Adobe Commerce store using the ALTER TABLE
SQL statement.
You might need to change the increment ID for new DB entities in these cases:
You may also fix the payment gateway issue for PayPal by allowing multiple payments per invoice ID in PayPal’s Payment Receiving Preferences. See PayPal gateway rejected request - duplicate invoice issue in our support knowledge base.
SHOW TABLE STATUS FROM `{database_name}` WHERE `name` LIKE 'sequence_{entity_type}_{store_id}';
If you are checking an auto-increment for an order on the store with ID=1, the table name would be:
'sequence_order_1'
If the value of the auto_increment
column is 1234, the next order placed at the store with ID=1 will have the ID #100001234.
Update the entity using the following query:
ALTER TABLE sequence_{entity_type}_{store_id} AUTO_INCREMENT = {new_increment_value};
Important: The new increment value must be greater than the current one, not less!
After executing the following query:
ALTER TABLE sequence_order_1 AUTO_INCREMENT = 2000;
the next order placed at the store with ID=1 will have the ID #100002000.
Before executing the ALTER TABLE
query on the Production environment of Adobe Commerce on cloud infrastructure, we strongly recommend performing these steps: