I need to disable and enable a huge quantity of products filtering by sku and the easiest solution seem to be through Sql query but i have some issues to find the right code

For example i need to disable 10000 products Sku: 001, 002, 003, 004, etc... i've found the right code for magento 1.9 but for magento 2 it's not working

1 Answer 1


To disable product by using SKU. First of all find the attribute_id value for status. For find the correct attribute_id run this command in sql

select `eav_attribute`.`attribute_id` AS `attribute_id`, `catalog_product_entity_int`.`entity_id` AS `entity_id`, `catalog_product_entity_int`.`value` AS `value`, `catalog_product_entity`.`sku` AS `sku` from ((`eav_attribute` join `catalog_product_entity_int` on ((`eav_attribute`.`attribute_id` = `catalog_product_entity_int`.`attribute_id`))) join `catalog_product_entity` on ((`catalog_product_entity_int`.`entity_id` = `catalog_product_entity`.`entity_id`))) where ((`eav_attribute`.`attribute_code` = 'status') and (`catalog_product_entity_int`.`value` = 1))

The first value (for me was 97 ) is the correct attribute_id to replace at XXX

Copy the Attribute id and paste it in the SQL query run it in phpMyAdmin.

UPDATE catalog_product_entity_int a, catalog_product_entity b
SET a.value = "2"
WHERE b.entity_id = a.entity_id
AND b.sku LIKE '%SKU%'
AND a.attribute_id = **XXX**

After running the query Login to your admin and go to

System > Cache Management - > Clear Cache System > Index management > Rebuild Catalog Index


You can also change status massively via products grid: check the boxes for items -> Actions - Change status -> Enable/Disable

  • Hi, Thank you for reply, i've already tried with this code but returns 0 row updated Nov 19, 2020 at 11:25
  • You can also disable products using csv in magento Nov 19, 2020 at 11:42
  • i have updated answer. Nov 19, 2020 at 11:44
  • yes i know this option but even if rare this time i should disable over 17000 product and select one for one is impossible for me Nov 19, 2020 at 11:54
  • UPDATE ma_catalog_product_entity_int SET VALUE = '2' WHERE attribute_id = 80 //product status attribute it. AND entity_id IN (SELECT sku FROM ma_catalog_product_entity); Nov 19, 2020 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.