Fast bulk product status change with Magento

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

quicksql

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

rebuildindex

  • http://micflan.com/ Michael Flangan

    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

  • https://www.sonassi.com ben@sonassi.com

    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

    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!

  • https://www.sonassi.com ben@sonassi.com

    Thanks for your contribution Pedro, I’ve updated the article for you.

  • http://creative-ctrl.com JR Chew

    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

    I keep getting 0 rows affected….

  • angelica

    Hi, those commands didn’t change the STATUS of magento products, they changed their VISIBILITY….

  • https://www.sonassi.com Benjamin

    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

    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/

  • King Lucky Khan

    Check this free magento extension by magerevol (Quick Product Edit) for editing basic product info right on the grid without page reload.

    http://magerevol.com/magento-extensions/quick-product-edit.html