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.
5 comments
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
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.
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!
Hi Branko,
Can you help me? I’m looking for a way to automaticly change a Magento product status to unpublished when it’s no longer featured.
Grtz,
Pieter
Thanks for this. What would the SQL be for Magento 1.4.0.1? The database structure has changed.