Magento have 26500 products are available and I want to disable all this product using MySQL because via Magento take lots of time. all products attributes set name is Diamond and Diamond id is 9. Please help me in this case.


First of all, DON'T think the writing SQL way. It's because the "Status" attribute id might NOT identical in each version. Modifying records directly can cause unexpected result.

Instead, I'll recommend you to use export CSV way to do your task.

  1. Login to admin panal, go to System=>Import/Export=>Export

  2. Select "Products" on "Entity Type", and click "Continue" on the bottom.

  3. Wait for a moment, the browser will prompt you to download a CSV file.

  4. Open the CSV file using MS Excel or other equivalent spreadsheet software.

  5. Here I use Excel 2016 as example. Go to Sort & Filter and select Filter

  6. Click the filter under _attribute_set and select Diamond

  7. Move to status, change all the values to 2 and save the CSV.

  8. Go back to admin panal, then go to System=>Import/Export=>Import

  9. Select "Products" on "Entity Type" and "Replace Existing Complex Data".

  10. After selecting the modified CSV, click "Check Data". Your data should be valid. And click "Import" after that.

Tested on Magento EE, equivalent to CE

  • Thank you PY Yick it's working fine. Any other solution for Delete products ? – Dhirendrasinh DRC Dec 7 '17 at 9:01
  • 1
    A much better answer than mine :) Choose this one! The only thing I would say is don't use MS Excel. Use OpenOffice and save the csv as UTF-8 encoding. – jscar Dec 7 '17 at 9:01
  • @DhirendrasinhDRC For delete products, simply keep the products that WANT to delete. And on step 9, select "Delete Entities" – PY Yick Dec 7 '17 at 9:09

You can do this with a product csv import. Export and filter using the relevant attribute and in the csv change the status to 2 and re-import. It's a lot safer doing it this way than a direct SQL query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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