Bulk disable multiple Magento products

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.

You made it all the way down here so you must have enjoyed this post! You may also like:

How to manage clients, pressure and stress Zelimir Gusak
Zelimir Gusak, | 3

How to manage clients, pressure and stress

Filter order grid by multiple ID’s Petar Sambolek
Petar Sambolek, | 14

Filter order grid by multiple ID’s

Consuming Magento REST service using Zend_OAuth_Consumer Darko Goles
Darko Goles, | 45

Consuming Magento REST service using Zend_OAuth_Consumer

5 comments

  1. I have faced issue to execute this sql query because in catalog_product_entity table there is no column for category_ids. I am using magento 1.9.3.4 so what i create an alternative query as following

    =====================================================================
    UPDATE catalog_product_entity_int
    SET `value` = ‘2’
    WHERE attribute_id = 273
    AND entity_id IN (SELECT product_id FROM catalog_category_product WHERE category_id = ‘219’);
    ===================================================================================

    in my case ID for status attribute is 273 and category which i am using has ID 219.

    @all, can anyone point out any issue for this query for magento 1.9.3.4 please

    cheers

  2. 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.

  3. 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>.

Tell us about your project

Drop us a line. We'd love to know more about your project.