If you have a large catalogue, using some standard features are simply impractical to use. Recently, we needed to disable an entire range of products based on its SKU – via the backend, bulk status changing 5000 products will take hours, but fortunately SQL is a spot more lightweight.
In the code below (to be run via command line MySQL or phpMyAdmin), just replace %SKU% with your SKU identifier and use % as a wildcard or _ as a single character wildcard.
This will obviously apply to any other attribute, so just change the code as necessary.
enabled = 1 disabled = 2
UPDATE catalog_product_entity_int cpei, catalog_product_entity cpe SET cpei.value = "2" WHERE cpe.entity_id = cpei.entity_id AND cpe.sku LIKE '%SKU%' AND cpei.attribute_id = 273
Disable by category
Another resource for disabling by category is
UPDATE catalog_product_entity_int cpei, catalog_product_entity cpe SET value = '2' WHERE cpe.entity_id = cpei.entity_id AND attribute_id = "80" AND cpe.category_ids = "%35%" AND (cpe.category_ids LIKE "35,%" OR cpe.category_ids LIKE "%,35,%")
Remember to rebuild the category indexes
After the MySQL above has run – you’ll need to let Magento take the reigns back a little to update the category indexes, this is quite straightforward, but a little time consuming.
Login to your admin and go to System > Cache Management, the select Rebuild Catalog Index
Michael Flangan
27 Jan. 2010
cpei.attribute_id = 273 returns 0 rows.
attribute_id’s I have in there are 80, 81, 85 and 467.
Do any of these relate to product status? Using Magento ver. 1.3.2.4
ben@sonassi.com
28 Jan. 2010
Hi Michael,
The easiest way to tell is if you go into:
Admin > Catalog > Attributes > Manage Attributes
Then in the “Attribute Code” box, enter “status” then press “Search”. Click the attribute result “status” – then look at the digits at the very end of the URL …
catalog_product_attribute/edit/attribute_id/XXX/
Pedro Agrelos
16 Sep. 2010
Hi
Take come caution when executing a query like:
UPDATE catalog_product_entity_int cpei, catalog_product_entity cpe
…
AND cpe.category_ids = “%35%”
because category_ids will match 35, 135, 235…
To update only category 35 must be something like
AND (cpe.category_ids LIKE “35,%” OR cpe.category_ids LIKE “%,35,%”)
Take care!
ben@sonassi.com
16 Sep. 2010
Thanks for your contribution Pedro, I’ve updated the article for you.
JR Chew
4 Jan. 2011
You’ve saved me a ton of time. It seems that the only other way to do this is to write a Magento module which is a bit overkill here.
Thanks again!
Rushi
14 Feb. 2011
I keep getting 0 rows affected….
angelica
27 Apr. 2011
Hi, those commands didn’t change the STATUS of magento products, they changed their VISIBILITY….
Benjamin
27 Apr. 2011
Well, it will change any attribute, in our example, we used attribute_id 273 – however, it could be different on your store. Have a look at the above post to Michael