reset next order number

i know order numbers are based on post id in wp

but if i remember my first test order was #85 so after deleting them all i would be happy to start from #100 just to start from a round number

All WP objects use post IDs: posts, pages, images, products, revisions to any of these and orders, order refunds and more. The next any-of-these will use the next available post ID. Post IDs always increment and cannot be reset – otherwise WP would lose track of all the objects in the database.

“WooCommerce Sequential Order Numbers” worked for me – suggest you persevere with it and try its support forum in case of difficulty. The order ID is still the next post ID, but the plugin will mask this and create and show the order number from order metadata. Its not perfect because urls will still use the order ID. I think using it is a one way option – if you deactivate the plugin, the order numbers won’t be accessible.

yes but if the only thing that changes are orders then i should be able to get back to the first order number right?

think i figured it

BIG Assumption: Nothing but orders will be created in the future

delete all order data with these sql commands:

DELETE FROM wp_woocommerce_order_itemmeta
DELETE FROM wp_woocommerce_order_items
DELETE FROM wp_comments WHERE comment_type = ‘order_note’
DELETE FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = ‘shop_order’ )
DELETE FROM wp_posts WHERE post_type = ‘shop_order’

look in table wp_post for the highest post id, in my case it was 85 which fits well with my first test order

to start with a round number i picked 100, it could be 1000 also

now reset the next post id with this sql command

ALTER TABLE wp_posts AUTO_INCREMENT = 100

In theory maybe, try it. In practice, the post IDs tend to creep up relentlessly. Any new products, product revisions or order refunds would increment the post iD.

Lorro is correct, in that there’s not really a great way to get back to “used” order numbers, since they are just post IDs.

I think your solution would also work, as long as your BIG assumption is correct, and there are no more products, revisions, images, anything added to your site. That isn’t typically realistic given the nature of sites.