Reset Product ID

[frii]

Hello, I have been searching for a while but haven't found the answer

 

Is there any way to reset the product ID? I have insert and deleted many products and I just realized that the deleted product's id is not being re-occupied, for example I have #1 to #14 ID then I delete #10 and #11, when I add new product it becomes #15, not taking the #10 spot

 

Is there any way to re generate the IDs so the vacant IDs can be used? because at this rate the ID will be only piled up

Edited March 1, 2015 by frii (see edit history)

[frii]

bump

 

please help

[danplant]

Also me !

Please help !

[rocky]

It's not possible to set up MySQL to always use vacant IDs. You can reset the AUTO_INCREMENT value, but then it will increment to the next ID even if it's already taken. I suppose you could set the AUTO_INCREMENT to 15, then after adding a product, change it to the next vacant ID, but that's a lot of work.

[danplant]

every day import thousands of products and id increase (now I have id # 56490) and will increase again ... you can not bring it back to id # 1?

[fedesib]

Hi there,

 

as Rocky said it is not possible to tell MySQL to use old IDs. Either you truncate the table and start again from the beginning (but I don't think this is a viable solution, specially if you are working on a live site) or you keep the authomatic IDs. By the way, what's your concern about "big numbers" for IDs?

 

Best,

Federica

[rocky]

You can use a query like the following to change the AUTO_INCREMENT value, but you should only set it to the next ID after the current highest ID, otherwise you'll get a "Duplicate row" error when adding a new product with an existing ID:

ALTER TABLE ps_product AUTO_INCREMENT = 1

Change ps_ to your database prefix and 1 to the ID you want to be next.

[danplant]

Hi there,

 

as Rocky said it is not possible to tell MySQL to use old IDs. Either you truncate the table and start again from the beginning (but I don't think this is a viable solution, specially if you are working on a live site) or you keep the authomatic IDs. By the way, what's your concern about "big numbers" for IDs?

 

Best,

Federica

if every day import 3000+ products, in 365 days (1 year...) the "big numbers" will be over the million...