I am using CE, I want to disable all products from one specific category.

How I can disable all products from category? (SQL is fine)

  • can you please tell me? if a product would also included in other categories then what would be happen – Amit Bera Jan 16 '15 at 13:06
  • if product is in another category as well even then product should be disable. – Sandesh Jan 16 '15 at 13:23

Create a new file in in your Magento root, call it products-disable.php and paste in the following


    $categoryId = 123;
    $category = Mage::getModel('catalog/category')->load($categoryId);

    if ($category->getId()) {
        $products = $category->getProductCollection();
        foreach ($products as $product) {
            $product->setData('status', false);
    } else {
        die("category failed to load");

Then browse to your site http://mymagesite.url/products-disable.php and it should set the status of all the products in that category as false


edit :

This magento code will update your database as a bulk action, so far quicker than the initial save on each row. I've batched it in pages of 500, change that value in the setPageSize call to whatever you please. I'd recommend this method FAR more than doing a direct sql change as it will trigger events and indexes and stuff.

I haven't tested this code, but believe it should work.


$categoryId = 123;
$storeCode = 'default';

$storeId = Mage::getModel('core/store')->load($storeCode)->getId();
$category = Mage::getModel('catalog/category')->load($categoryId);

if ($storeId && $category->getId()) {

    $collection = $category->getProductCollection();


    for ($curPage = 1; $curPage <= $collection->getLastPageNumber(); $curPage++) {

        $ids = array();
        foreach ($collection as $_product) {
            $ids[$_product->getId()] = $_product->getId();

        if (!empty($ids)) {
            Mage::getSingleton('catalog/product_action')->updateAttributes($ids, array('status' => Mage_Catalog_Model_Product_Status::STATUS_DISABLED), $storeId);

} else {
    die("category or store failed to load");
  • Thanks Luke. Is there any way we can do it using SQL? As I have more than 50000 products in one category. – Sandesh Jan 16 '15 at 13:39
  • Gotcha, give me 5 mins :) – Luke Rodgers Jan 16 '15 at 13:42
  • See my edit for updated code – Luke Rodgers Jan 16 '15 at 13:49
  • For example category has 2000 products. How I can disable all products in this category using your script? – Sandesh Jan 16 '15 at 14:09
  • 1
    Second approach much better than the first one. +1 – Marius Jan 16 '15 at 14:35

Hi try this which is give you list of products specifiv cat

For getting this category products:

SELECT `e`.`value_id` FROM `catalog_product_entity_int` AS `e` 
    LEFT JOIN `catalog_category_product` AS `cp` ON e.entity_id = cp.product_id WHERE (cp.category_id IN(15)) and e.attribute_id = 96 )

and disable the product by

update  `catalog_product_entity_int` set  `catalog_product_entity_int`.value_id =0 where `catalog_product_entity_int`.`value_id` in (YOUR_UPPer_value_Ids)

Where 15 in category ids

  • what is product.attribute_id? – Sandesh Jan 16 '15 at 13:53
  • I believe thats the default magento attr id for "status". It's unlikely that it's different on your instance, but it is possible! This is why I prefer the direct Magento approach, works on all systems. select attribute_id, attribute_code,source_model from eav_attribute where attribute_code ='status'; shows 96 as the attribute_id on my instance – Luke Rodgers Jan 16 '15 at 13:54

FOR MAGENTO 2 Disable All products from a specific category.

UPDATE `catalog_category_product` JOIN `catalog_product_entity_int` ON catalog_category_product.product_id = catalog_product_entity_int.entity_id SET catalog_product_entity_int.value = 2 WHERE catalog_category_product.category_id=1754 AND catalog_product_entity_int.attribute_id = 94

Run This query.

  • attribute id 94 taken from eav_attribute table. i.e enable/disable field of product
  • Category id has taken static because I want to update from this category.
  • value=2 means disable the product

So this query is taking all products from catalog_category_product.product_id WHERE category_id= 1754. And then matching product id and entity id in catalog_product_entity_int and updating value in catalog_product_entity_int.value column as disable i.e 2

New contributor
Nilesh Dhumal is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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