Bulk disable multiple Magento products

With all its ORM, model, resource and so on greatness, sometimes platform limitations are too obvious in Magento when it comes to large number of products in store. Recently I faced a simple task, I needed to disable around 6500 products in Magento that were assigned to a single category called “Inactive Products”. Given that it was an import from old system to Magento it is irrelevant why one would use category “Inactive Products” instead of just setting their status to “disabled”. My point is, whats the fastest way to do a bulk action on large number of products.

There are certain bulk actions you can do from “Catalog > Manage Products” section, however they all fail on large number of products selected. Logical solution is to simply use the raw SQL query. Only one catch, knowing where to look for.

Below is a practical example on “How to bulk update product status based on product category”. In my case, the id of my category in question was 35, while my “status” attribute had an id of 80. Most likely yours “status” attribute will have the same id value given that its a default Magento attribute.

Here is the actual SQL code that disables the products which have only one category assigned, the one with id 35:

UPDATE ma_catalog_product_entity_int
SET value = '2'
WHERE attribute_id = 80
AND entity_id IN (SELECT entity_id FROM ma_catalog_product_entity WHERE category_ids = '35');

Code above executed under two seconds, disabling around 6500 products in one run. Doing something like this purely from PHP or shall I say Magento can turn out to be “mission impossible”.

Only one advice, always do a full database backup prior to any database changes.