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.


4 comments

  1. Hi Branko,
    I personally do this by means of inventory multi editor of Store Manager for Magento which can massively manage products inventory – update Magento products quantity, control products status, change the minimum quantity of products for status to be “Out of stock”, etc. Anyway best information I found so far as concerned with magento bulk editing. I’m looking forward for more of your posts.

  2. Thank you very much!

    I have to put on Disable products from a lists of SKU’s.
    The status id is 84.

    Your query is exactly what I need and I didn’t found it anywhere on web.

    Thanks again!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.