I need to move from my old version of my site to my new one. I have imported all the orders although I did this manually as the products structures completely changed. I would like to know a reliable way to change the auto increment of all the tables: orders, invoices, transactions etc so that when a customer phones up they don't have an order number someone else once had.
First, make a backup of your DB.
Then you can try running the following SQL statement to change where your orders / invoices begin:
ALTER TABLE sequence_order_1 AUTO_INCREMENT=0000000910;
This will increment orders starting at 0000000910 for example. Change this to whatever you need.
You can also do this for other tables as well, such as invoices:
ALTER TABLE sequence_invoice_1 AUTO_INCREMENT=0000000910;
ALTER TABLE sequence_shipment_1 AUTO_INCREMENT=0000000910;
Go to your database in phpmyadmin,
Here _1 is used for store id after tablename.
Default Frontend store id is 1. if you have multi store then you have to set query for each store with table name like sequence_order_2 upto sequence_order_.*
Enter below query for table sequence_order_1 is used for default store. If you have multiple store you have to set tablename as per store id in below query.
For order placed from frontend.
sequence_order_1 is used for order id management in magento 2.
ALTER TABLE sequence_order_1 AUTO_INCREMENT=202000001;
Next order id is start from 202000001.
For INVOICE, if you want to change invoice id
ALTER TABLE sequence_invoice_1 AUTO_INCREMENT=202000001;
ALTER TABLE sequence_shipment_1 AUTO_INCREMENT=202000001;