13

Is there any way to delete multiple products by filtering with SKU? I have around 1000 products that I want to delete.

9 Answers 9

20

The fastest way to do it is to run this query directly.

DELETE FROM `catalog_product_entity` WHERE `sku` IN ('SKU1', 'SKU2', ...., 'SKU1000');

Everything should cascade nicely. attribute values will be deleted, category relations will be deleted, upsells, crosssells and related and so on.

[EDIT]
There is a catch to this. Thanks to STW for spotting this. The reviews and ratings will remain orphans because there is no foreign key to the products table for them.

5
  • 6
    dont forget to reindex after this (if you use flat product catalog) Jun 29, 2014 at 6:43
  • 2
    This is basically what the Admin Import/Export tool does when using it to perform mass deletions. It is extremely fast compared to calling ->delete() but will dodge any code listening for events. In stock Magento it appears that reviews and ratings will be orphaned (they won't be deleted when their associated product is).
    – STW
    Oct 1, 2015 at 13:22
  • @STW. Nice catch. I completely forgot about the reviews and ratings.
    – Marius
    Oct 1, 2015 at 13:24
  • I'm not sure whether it is good idea or not. What about attribute data which saved in eav tables ? are those deleted too ? Jun 28, 2016 at 7:44
  • @AnuragPatbandha. all the catalog_product_entity_* tables have a FK on the entity_id field to the catalog_product_entity.entity_id field. They should cascade nicely.
    – Marius
    Jun 28, 2016 at 7:54
19

All due respect to Marius, but please don't interact with the database directly if it's at all avoidable. Maybe the related tables will be updated automatically, if your release of Magento and all your extensions are bug-free in all the right places. But if they're not, that sort of thing can destroy your site.

Instead, you can use Magento's own CSV import feature.

Just list your SKUs in a file, simple as:

sku
ABC1
ABC2
ABC3

...etc. Then save as a CSV file.

Then, in System > Import/Export > Import, select Entity Type: Products and Import Behaviour: Delete Entities, and import this file. And that's it!

3
  • 2
    fyi - generally I agree that avoiding direct DB interaction is best; however this is actually how Magento deletes products via the Admin Import tool (see Mage_ImportExport_Model_Import_Entity_Product::_deleteProducts())
    – STW
    Sep 30, 2015 at 19:38
  • suppose if i want to delete products from the selected store, what is my CSV format.
    – zus
    Dec 21, 2017 at 7:21
  • If you just want to unassign products from a selected store but not remove them from the database, you could try a CSV import with two columns: sku and store, make sure the store column contains only those store IDs that you want the product assigned to, select Import Behaviour: update, and import that. I haven't tested this though, so proceed with caution! (If I find time I'll test this later and add another comment) Dec 21, 2017 at 16:48
7

You can do it programmatically. Create a skustodelete.csv listing all skus to be deleted and after that here is the code to proceed further

    require_once 'app/Mage.php';
    Mage :: app("default")->setCurrentStore(Mage_Core_Model_App :: ADMIN_STORE_ID);
    $skuAll = array();
    $file_handle = fopen("skustodelete.csv", "r");
    $catalog = Mage::getModel('catalog/product');
   while (!feof($file_handle)) {
     $line_of_text = fgetcsv($file_handle, 1024);
     $allSku = $line_of_text[0];
     $product = $catalog->loadByAttribute('sku', $allSku);
     try {
          $product->delete();
          echo "Product with ID: " . $product->getId() . " Deleted Successfully". PHP_EOL;
     } catch (Exception $e) {
          echo "Product with ID: " . $product->getId() . "cannot be deleted" . PHP_EOL;
     }
}
echo "Finish Delete";
1
  • This one works great except you'll want to add an (if $product) around the try section or it'll crash on trying to call delete() on a boolean.
    – Mike
    Sep 30, 2021 at 14:41
4

Kevin S, you can delete product by csv file. Just follow the billow step

Step 1 :

Create a csv file and past the skus which you want to delete from system. Give the file name as skus.csv

Step 2 :

Create & replace a php file in your root directory. Then past the following code

require_once '../app/Mage.php';
Mage :: app("default") -> setCurrentStore( Mage_Core_Model_App :: ADMIN_STORE_ID );
$skuAll =array();
$file_handle = fopen("skus.csv", "r");
 while (!feof($file_handle) ) {
    $line_of_text = fgetcsv($file_handle, 1024);
$allSku = $line_of_text[0];

}
$products = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect('*') 
    ->addAttributeToFilter(
        'sku', array('in' => $allSku)
    )
    ->load();

    if(is_array($products))
    {
        foreach ($products as $key => $pId)
        {
            try
            {
                $product = Mage::getModel('catalog/product')->load($pId)->delete();
                echo "successfully deleted product with ID: ". $pId ."<br />";
            } 
            catch (Exception $e) 
            {
                echo "Could not delete product with ID: ". $pId ."<br />";
            }
        }
    }

Note : I recommended to you before run this code you must get the backup. I hope its will be help you.

3

The none code way

In the admin section under Manage Products would will find a grid with all your products. There is a column called SKU. Here you can filter your products based on the value.

sku filter

Then once you have filtered by SKU you can use the checkboxes on the left hand side to select all the items you wish to delete.

multiple select

Notice the select all which will select all items in the complete grid and select visible which will only select items on the current page of the grid.

Once you have selected the items you want you can use the mass action button on the top right hand side of the grid and select the delete option.

delete mass action

This will prompt you to make sure you a sure you want to delete these items. After selecting yes to this pop-up it will proceed to delete your items. Depending on your index settings you may have to run a re-index after this process.

1
  • 2
    And Select All works with terrifying efficiency. Accidentally choosing it without any sort of search constraints truly deletes everything in your product catalog, and once started, killing the server or running out of memory during the delete are the only things that stop it. Jun 29, 2014 at 15:09
1

Because list tables

  • catalog_product_entity_varchar,
  • catalog_product_entity_tier_price,
  • catalog_product_entity_media_gallery,
  • catalog_product_entity_media_gallery_value,
  • catalog_product_entity_text,
  • catalog_product_entity_group_price,
  • catalog_product_entity_datetime,
  • catalog_product_entity_decimal,
  • catalog_product_entity_int
  • catalog_category_product
  • catalog_product_link

are foreign keys of catalog_product_entity.

So when delete an record in catalog_product_entity, some records of above tables will be deleted too.

Code (implement Marius's answer) delete all product which have entity_id <= 18069 :

$adapter = $setup->getConnection("catalog_write");

$where = array(
    'entity_id <= ?' => '18069'
);

$tableCatalogProduct = Mage::getModel("catalog/product")->getResource()->getEntityTable();
$adapter->delete($tableCatalogProduct, $where);


$tableRating = Mage::getModel("rating/rating")->getResource()->getMainTable();
$adapter->delete($tableRating,  $where);

$tableRating = Mage::getModel("review/review")->getResource()->getMainTable();
$adapter->delete($tableRating, $where);
0
delete the products against skus

   ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 0); 
set_time_limit(0);
error_reporting(E_ALL);
require __DIR__ . '/app/bootstrap.php';
$bootstrap = \Magento\Framework\App\Bootstrap::create(BP, $_SERVER);

$objectManager = $bootstrap->getObjectManager();

$state = $objectManager->get('Magento\Framework\App\State');
$state->setAreaCode('adminhtml');

$skuAll =array();
$file_handle = fopen("skus.csv", "r");
$header=fgetcsv($file_handle);
$product=$objectManager->create('Magento\Catalog\Model\Product');
//$row=1;
 while ($row = fgetcsv($file_handle, 40000, ",") ) {
    // $data_count = count($row);
    // if ($data_count < 1) {
    //     continue;
    // }
    $data =array();
    $data = array_combine($header, $row);
    // $$data['sku'] = 'abc';
    $allSku = $data['sku']; 
    // echo $allSku;
    $loadProducts=$product->loadByAttribute('sku', $allSku);
     try {
          $loadProducts->load($allSku)->delete();
          echo "Product with Sku: " . $loadProducts->getSku() . " Deleted Successfully". PHP_EOL;
     } catch (Exception $e) {
          echo "Product with SKU: " . $loadProducts->getSku() . " cannot be deleted" . PHP_EOL;
     }
}
0

Best option is to create a CSV with SKU as the column and use the Product import feature, setting the behavior to "DELETE"

-1

$productRepository = $objectManager->get('\Magento\Catalog\Model\ProductRepository');

$registry = $objectManager->get('\Magento\Framework\Registry');

$registry->register('isSecureArea', true);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.