Remove unused attribute options in Magento 2

Whilst working on a catalog of 36000 items imported from a previous system we ended up with a lot of very poor "size attributes". A quick bit of Googling led us to this answer.

The script here can be used is MagManager or replace the /*PREFIX/* with your database prefix. Remember to always backup before doing stuff like this.

 /* This SQL block may be executed to remove unused attribute options. */
/* Please note that this SQL block must be modified manually according to your database specifics to work properly.  */ 
 
    DELETE o, v
    FROM `/*PREFIX*/eav_attribute` a
INNER JOIN `/*PREFIX*/eav_attribute_option` o ON a.`attribute_id` = o.`attribute_id`
INNER JOIN `/*PREFIX*/eav_attribute_option_value` v ON v.`option_id` = o.`option_id`
INNER JOIN `/*PREFIX*/eav_entity_type` t ON t.`entity_type_id` = a.`entity_type_id`
 LEFT JOIN `/*PREFIX*/catalog_product_entity_int` pi ON o.`option_id` = pi.`value` AND o.`attribute_id` = pi.`attribute_id`
 LEFT JOIN `/*PREFIX*/catalog_product_entity_varchar` pv ON FIND_IN_SET(o.option_id, pv.value) AND o.`attribute_id` = pv.`attribute_id`
     WHERE pi.`entity_id` IS NULL
       AND pv.`entity_id` IS NULL
       AND t.`entity_type_code` = "catalog_product"
       AND a.is_user_defined = 1

Support Knowledge Base

Remove unused attribute options in Magento 2

Analytics

Related content