You are viewing an old revision of this post, from October 28, 2014 @ 13:42:51. See below for differences between this version and the current revision.

Deleting All Products from a magento database with SQL

It doesn’t happen often, but sometimes I need to wipe all products from a magento database during development. Either an import was written slightly off, or it was just full of test data and it’s cleaner to wipe the products instead of deleting them through the admin.

A simple script for this found in a few spots on the internet, but the most updated one for Magento 1.8 is:

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `catalog_product_bundle_price_index`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_bundle_selection_price`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
truncate table `catalog_product_entity_group_price`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_flat_1`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_category_product_index`;

TRUNCATE TABLE `catalog_category_product`;

TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `catalog_product_relation`;

TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
SET FOREIGN_KEY_CHECKS = 1;

Revisions

Revision Differences

October 28, 2014 @ 13:42:51Current Revision
Content
Unchanged: <div class="entry-content" style="border: 0px;margin: 0px;padding: 12px 0px 0px;vertical-align: baseline;clear: both;font-family: Georgia, 'Bitstream Charter', serif;font-size: 16px;line-height: 24px"> Unchanged: <div class="entry-content" style="border: 0px;margin: 0px;padding: 12px 0px 0px;vertical-align: baseline;clear: both;font-family: Georgia, 'Bitstream Charter', serif;font-size: 16px;line-height: 24px">
Unchanged: <p style="border: 0px;margin: 0px 0px 24px;padding: 0px;vertical-align: baseline;background: transparent"> Unchanged: <p style="border: 0px;margin: 0px 0px 24px;padding: 0px;vertical-align: baseline;background: transparent">
Unchanged: It doesn&rsquo;t happen often, but sometimes I need to wipe all products from a magento database during development. Either an import was written slightly off, or it was just full of test data and it&rsquo;s cleaner to wipe the products instead of deleting them through the admin. Unchanged: It doesn&rsquo;t happen often, but sometimes I need to wipe all products from a magento database during development. Either an import was written slightly off, or it was just full of test data and it&rsquo;s cleaner to wipe the products instead of deleting them through the admin.
Unchanged: </p> Unchanged: </p>
Unchanged: <p style="border: 0px;margin: 0px 0px 24px;padding: 0px;vertical-align: baseline;background: transparent"> Unchanged: <p style="border: 0px;margin: 0px 0px 24px;padding: 0px;vertical-align: baseline;background: transparent">
Unchanged: A simple script for this found in a few spots on the internet, but the most updated one for Magento 1.8 is: Unchanged: A simple script for this found in a few spots on the internet, but the most updated one for Magento 1.8 is:
Unchanged: </p> Unchanged: </p>
Unchanged: <p> Unchanged: <p>
Unchanged: SET FOREIGN_KEY_CHECKS = 0; Unchanged: SET FOREIGN_KEY_CHECKS = 0;
Unchanged: </p> Unchanged: </p>
Unchanged: <p> Unchanged: <p>
Unchanged: TRUNCATE TABLE `catalog_product_ bundle_price_index`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ bundle_price_index`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ bundle_selection`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ bundle_selection`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ bundle_selection_price`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ bundle_selection_price`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ bundle_option_value`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ bundle_option_value`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ bundle_option`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ bundle_option`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ entity_datetime`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ entity_datetime`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ entity_decimal`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ entity_decimal`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ entity_gallery`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ entity_gallery`;<br />
Unchanged: truncate table `catalog_product_ entity_group_price`;<br /> Unchanged: truncate table `catalog_product_ entity_group_price`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ entity_int`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ entity_int`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ entity_media_ gallery`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ entity_media_ gallery`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ entity_media_ gallery_value`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ entity_media_ gallery_value`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ entity_text`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ entity_text`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ entity_tier_price`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ entity_tier_price`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ entity_varchar`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ entity_varchar`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_flat_1`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_flat_1`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_link`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_link`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ link_attribute_ decimal`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ link_attribute_ decimal`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ link_attribute_int`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ link_attribute_int`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ link_attribute_ varchar`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ link_attribute_ varchar`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_option`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_option`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ option_price`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ option_price`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ option_title`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ option_title`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ option_type_price`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ option_type_price`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ option_type_title`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ option_type_title`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ option_type_value`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ option_type_value`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ super_attribute_label`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ super_attribute_label`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ super_attribute_ pricing`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ super_attribute_ pricing`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ super_attribute`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ super_attribute`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ super_link`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ super_link`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ enabled_index`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ enabled_index`;<br />
Unchanged: TRUNCATE TABLE `catalog_product_ website`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_ website`;<br />
Unchanged: TRUNCATE TABLE `catalog_category_ product_index`; Unchanged: TRUNCATE TABLE `catalog_category_ product_index`;
Unchanged: </p> Unchanged: </p>
Unchanged: <p> Unchanged: <p>
Unchanged: TRUNCATE TABLE `catalog_category_product`; Unchanged: TRUNCATE TABLE `catalog_category_product`;
Unchanged: </p> Unchanged: </p>
Unchanged: <p> Unchanged: <p>
Unchanged: TRUNCATE TABLE `catalog_product_entity`;<br /> Unchanged: TRUNCATE TABLE `catalog_product_entity`;<br />
Deleted: truncate table `catalog_product_relation`;  Added: TRUNCATE TABLE `catalog_product_relation`;
Unchanged: </p> Unchanged: </p>
Unchanged: <p> Unchanged: <p>
Unchanged: TRUNCATE TABLE `cataloginventory_ stock_item`;<br /> Unchanged: TRUNCATE TABLE `cataloginventory_ stock_item`;<br />
Unchanged: TRUNCATE TABLE `cataloginventory_ stock_status`;<br /> Unchanged: TRUNCATE TABLE `cataloginventory_ stock_status`;<br />
Unchanged: SET FOREIGN_KEY_CHECKS = 1; Unchanged: SET FOREIGN_KEY_CHECKS = 1;
Unchanged: </p> Unchanged: </p>
Unchanged: </div> Unchanged: </div>

Note: Spaces may be added to comparison text to allow better line wrapping.

No comments yet.

Leave a Reply