How to mass delete products from disabled categories?

Question

I have a lot of products that are part of disabled categories. Is there a way for me to delete them all?

I want to tell it the ID of the category and then delete all the products under that category.

Is this possible?

Thank you very much!

Answer 1

Replace @categoryId = 5 with your category id.

SET @categoryId = 5;  
DELETE FROM `catalog_product_entity` 
where `entity_id` IN 
 (select `product_id` from `catalog_category_product`
where `category_id` = @categoryId);

Note: Take backup first before hitting any direct query on DB.

Answer 2

to delete permanently products of disable categories use following code:-

<?php
set_time_limit(0);
ini_set('memory_limit','512M');
require_once('app/Mage.php');
Mage::app("default")->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
umask(0);

$category_id = 45;
$products_collection = Mage::getModel('catalog/category')->load($category_id)->getProductCollection();

foreach($products_collection as $product){
    try{
        $product->delete();
    }catch(Exception $e){
        Mage::log("Unable to delete product with ID: ".$product->getId().");
    }
}

Note:- If your product which is under disabled category as well as enabled category then product as product is getting deleted the product will be gone from the enable category as well