Magento

Is there a way to do it with an SQL query ?

If you wish to delete all products from all the stores:

If only for one store (2 is store_id you wish to clean):

Moshe,
I’ve been running into a lot of problems importing products. Then I’m left with junk in the product tables.  I tried cleaning out the catalog_product_entity like you said, but now I’m getting this error on import.

This error:
# SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘4-1’ for key 2

Are there more tables I need to clear out to get rid of all previous product import attempts or will this clear out all the tables that are linked to it?
Thanks!

iI use this SQL for delete all products:

I used it while testing product upload
It’s also set the first product ID to 1

Shay

delate_all_prodact.sql  (File Size: 2KB - Downloads: 164)

With the Massive action you can delete easely all products

Catalog>manage product : select all>action : delete > submit

But it doesn’t put the auto-increment at 1, so if you need this run shay’s script

Shay’s SQL is the most complete, but I’ve found additional tables that are also required to be deleted. My problem is that I know there are STILL MORE we are missing since I am still getting errors.  Please add to this if you can.

2. Run SQL:
TRUNCATE `catalog_product_website`;
TRUNCATE `catalog_category_product`;
TRUNCATE `catalog_product_entity`;
TRUNCATE `catalog_product_entity_datetime`;
TRUNCATE `catalog_product_entity_decimal`;
TRUNCATE `catalog_product_entity_gallery`;
TRUNCATE `catalog_product_entity_int`;
TRUNCATE `catalog_product_entity_media_gallery`;
TRUNCATE `catalog_product_entity_media_gallery_value`;
TRUNCATE `catalog_product_entity_text`;
TRUNCATE `catalog_product_entity_tier_price`;
TRUNCATE `catalog_product_entity_varchar`;

ALTER TABLE `catalog_product_website` AUTO_INCREMENT =1;
ALTER TABLE `catalog_category_product` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_datetime` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_decimal` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_gallery` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_int` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_media_gallery` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_media_gallery_value` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_text` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_tier_price` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_varchar` AUTO_INCREMENT =1;

I also found out that you have to:

DELETE FROM `core_url_rewrite`
WHERE `id_path` LIKE ‘PERCENT SIGN product PERCENT SIGN’

But still getting
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘1-1’ for key 2
on trying to import a product that was previously deleted. 

ally - 10 June 2008 12:42 PM

I also found out that you have to:

DELETE FROM `core_url_rewrite`
WHERE `id_path` LIKE ‘PERCENT SIGN product PERCENT SIGN’

But still getting
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘1-1’ for key 2
on trying to import a product that was previously deleted. 

I have figured out a way of getting rid of the constraint violation error… using the above and a bit of hunting and trial and error I have got the following queries to get rid of the error:

EDIT: ... I think this causes an error on the products page (ie kills it!)… so it is a query or two, too many! :(

I am only in my second week of magento, so use at own risk!

now this works a lot better so far, no duplication errors, doesn’t kill the product page

On our MySQL it seems that truncate resets the auto-increments, so we don’t need those extra commands