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

  • Alex

    We simply using extensions (Advanced Admin Products Manager by Iksanika) for such tasks which are not limited to status – it allow us to make changes specifiecly for each products in easy to use interface very quickly for any product attribute.

  • http://www.magentogems.com/ Zahid Mughal

    Best way is to disable through CSV. Make in Five minutes you can disable it without changing in the database level and i have also note that some values regarding the search changed if you run these quires. Why you don’t want to use the real and best method…. So, if you need this then follow my step…

    Export CSV and delete all the things except SKU and Status field . You can see status field value is 1, Now, change it to 2 and import again. make index management and BOOOOOOOOM.

  • Carlo

    Hi,
    if i want disable all products with no image, how can i change the code?

    Thanks

  • Leonardo Comerci

    First get the SKU’s that have no image with this SQL:

    SELECT cpe.sku
    FROM `catalog_product_entity_media_gallery` cpemg
    RIGHT OUTER JOIN catalog_product_entity cpe ON cpe.entity_id = cpemg.entity_id
    WHERE cpemg.`value` IS NULL and cpe.sku ”;

    Then use these SKU’s as set for the query in the post, like this:

    UPDATE catalog_product_entity_int cpei, catalog_product_entity cpe
    SET cpei.value = “2”
    WHERE cpe.entity_id = cpei.entity_id
    AND cpe.sku IN (
    SELECT cpe.sku
    FROM `catalog_product_entity_media_gallery` cpemg
    RIGHT OUTER JOIN catalog_product_entity cpe ON cpe.entity_id = cpemg.entity_id
    WHERE cpemg.`value` IS NULL and cpe.sku ”
    )
    AND cpei.attribute_id = 273;

    I hope this helps you.

    Regards,
    Leo.

  • Cristian Balan

    Ciao Leonardo.
    If you want to update all the associated product visibility’s to NOT VISIBLE INDIVIDUALLY, how you will do?