0

I uploaded a csv in which there were some products having no name, so it comes out blank in frontend.
I tried getting these products in catalog_product_entity_varchar but there were no NULL values in this table corresponding to attribute_id of Product Name attribute. Is there any way to delete these products using admin/sql query.

1 Answer 1

2

Get all products with empty name

SELECT main_tbl.* FROM catalog_product_entity main_tbl 
LEFT JOIN catalog_product_entity_varchar name_tbl ON main_tbl.entity_id = name_tbl.entity_id AND name_tbl.attribute_id = (
        SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product')
)
WHERE name_tbl.value IS NULL OR name_tbl.value = ''

Delete products with empty name

DELETE main_tbl FROM catalog_product_entity main_tbl 
LEFT JOIN catalog_product_entity_varchar name_tbl ON main_tbl.entity_id = name_tbl.entity_id AND name_tbl.attribute_id = (
        SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product')
)
WHERE name_tbl.value IS NULL OR name_tbl.value = '';
6
  • your delete query gives error , however select query is working fine Commented Nov 15, 2017 at 8:51
  • FIXED. Try once.
    – MagePsycho
    Commented Nov 15, 2017 at 8:52
  • it gives error again, near LEFT JOIN keyword Commented Nov 15, 2017 at 8:57
  • It's working fine for me. In worst case make SQL statement in one line.
    – MagePsycho
    Commented Nov 15, 2017 at 9:00
  • That's the standard JOIN should work for anyone. Try to google the error that you are having. Maybe the MySQL client or Server is too old?
    – MagePsycho
    Commented Nov 15, 2017 at 9:54

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.