Truncate Custom Attribute Label/Options using SQL Query

Question

I am using Magento 1.9.x.

I have created several custom attributes in Magento project. They all have thousands of labels or options. I don't want to remove custom attribute just want to delete all their values only.

For example I have one attribute as Manufacturer and 1200 options for that attribute now I want to delete all values of Manufacturer Attribute.

How can I do this using SQL queries?

I am following below process

1. Get attribute_id from Table: eav_attribute by searching attribute_code (In my case it is "manufacturer" and attribute_id is 81)
2. In table eav_attribute_option, searching 81 under attribute_id field will get us all option_ids
3. By taking all id's in eav_attribute_option_value, I am searching under option_id and getting value field and removing.

Final Query would be like:

DELETE eav_attribute_option , eav_attribute_option_value  FROM eav_attribute_option  INNER JOIN eav_attribute_option_value  
WHERE eav_attribute_option.option_id= eav_attribute_option_value.option_id and eav_attribute_option.attribute_id = 81

To perform such action for multiple Custom Attributes I need to run this query for multiple time.

Any specific solution??

Answer 1

Find Below Query

To view Options

SELECT `option`.`attribute_id`, `option`.`option_id`, 
        `attribute`.`attribute_code`, `value`.`value`
    FROM `eav_attribute_option` AS `option`
    INNER JOIN `eav_attribute` AS `attribute`
        ON `attribute`.`attribute_id` = `option`.`attribute_id`
    INNER JOIN `eav_attribute_option_value` AS `value`
        ON `value`.`option_id` = `option`.`option_id`
        WHERE `attribute`.`attribute_id`=81

To Delete Options

DELETE `value`,`option`
    FROM `eav_attribute_option` AS `option`
    INNER JOIN `eav_attribute` AS `attribute`
        ON `attribute`.`attribute_id` = `option`.`attribute_id`
    INNER JOIN `eav_attribute_option_value` AS `value`
        ON `value`.`option_id` = `option`.`option_id`
        WHERE `attribute`.`attribute_id`=81