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??

• Gopal Patel: stackoverflow.com/a/14252142/7089203
• Nidhishanker Modi: Thanks @GopalPatel I don't want to do it using Magento Programming. I want to achieve this using SQL Queries.

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