Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

How do I delete all products from a store ? 
 
Capitaine Commerce
Member
 
Avatar
Total Posts:  68
Joined:  2007-09-01
Lille, France
 

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

 
Magento Community Magento Community
Magento Community
Magento Community
 
Moshe
Magento Team
 
Avatar
Total Posts:  1770
Joined:  2007-08-07
Los Angeles
 

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

truncate table catalog_product_entity;

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

delete from catalog_product_store where store_id=2;
delete from catalog_product_entity_datetime where store_id=2;
delete from catalog_product_entity_decimal where store_id=2;
delete from catalog_product_entity_int where store_id=2;
delete from catalog_product_entity_varchar where store_id=2;
delete from catalog_product_entity_text where store_id=2;
delete from catalog_product_entity_gallery where store_id=2;
delete from catalog_product_entity_tierprice where store_id=2;
 
Magento Community Magento Community
Magento Community
Magento Community
 
ally
Jr. Member
 
Total Posts:  18
Joined:  2008-05-22
 

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!

 
Magento Community Magento Community
Magento Community
Magento Community
 
shay
Jr. Member
 
Total Posts:  14
Joined:  2008-05-01
israel
 

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

File Attachments
delate_all_prodact.sql  (File Size: 2KB - Downloads: 170)
 
Magento Community Magento Community
Magento Community
Magento Community
 
nicolas46
Sr. Member
 
Total Posts:  210
Joined:  2008-04-09
Toulouse
 

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

 
Magento Community Magento Community
Magento Community
Magento Community
 
ally
Jr. Member
 
Total Posts:  18
Joined:  2008-05-22
 

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;

 
Magento Community Magento Community
Magento Community
Magento Community
 
ally
Jr. Member
 
Total Posts:  18
Joined:  2008-05-22
 

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.  hmmm

 
Magento Community Magento Community
Magento Community
Magento Community
 
WebMonkey
Jr. Member
 
Total Posts:  27
Joined:  2008-06-05
 
ally - 10 June 2008 11:42 AM

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.  hmmm

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! :(

DELETED see below

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

 
Magento Community Magento Community
Magento Community
Magento Community
 
WebMonkey
Jr. Member
 
Total Posts:  27
Joined:  2008-06-05
 

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

# stock levels
    TRUNCATE cataloginventory_stock_item;

#bundles
    
TRUNCATE catalog_product_bundle_option;
    
TRUNCATE catalog_product_bundle_option_link;
    
TRUNCATE catalog_product_bundle_option_value;


# product data
    
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;
# end

# I think this is  Where configurable products are stored 
    
TRUNCATE catalog_product_super_attribute;
    
TRUNCATE catalog_product_super_attribute_label;
    
TRUNCATE catalog_product_super_attribute_pricing;
    
TRUNCATE catalog_product_super_link;
#

# assign the products to a website(s)
    
TRUNCATE catalog_product_website;

# Temp import data tables?
    
TRUNCATE TABLE dataflow_import_data;
    
TRUNCATE TABLE dataflow_batch_import;

#which products are in which category
    
TRUNCATE catalog_category_product;

# SEF URLs
    
DELETE FROM core_url_rewrite WHERE id_path LIKE '&#xpr;oduct%';

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

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top