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
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 = '';
-
your delete query gives error , however select query is working fine Nov 15, 2017 at 8:51
-
-
-
It's working fine for me. In worst case make SQL statement in one line. 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? Nov 15, 2017 at 9:54