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.

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 = '';
| improve this answer | |
  • your delete query gives error , however select query is working fine – Vivek Kumar Nov 15 '17 at 8:51
  • FIXED. Try once. – MagePsycho Nov 15 '17 at 8:52
  • it gives error again, near LEFT JOIN keyword – Vivek Kumar Nov 15 '17 at 8:57
  • It's working fine for me. In worst case make SQL statement in one line. – MagePsycho Nov 15 '17 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 Nov 15 '17 at 9:54

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.