Bulk Update Custom Attributes in Magento via the Database

Ok, here is something that according to most of the articles I have read is impossible, and it’s a relatively mundane task.

What I needed to do was having introduced a new Custom Attribute and set it’s default in Magento, I then needed to set all the products within the Magento Attribute Set to have that default applied to them, simple you would think but no not in Magento

So faced with the prospect of Manually having to go through hundreds of products to set the correct default on them all, my solution was to turn to the Magento database and populate the data manually.

And here’s how you do it.

First you will need the entity_type_id for this use the following SQL

SELECT entity_type_id FROM catalog_product_entity WHERE sku = ‘XXXX‘

In this data you can use a SKU that you know to be one of your target products and then get the entity_type_id and make a note of it, you are going to need it for the next step.

Next you need to get the correct attribute from the eav_attribute table the following SQL will return a list.

SELECT attribute_id, attribute_code FROM eav_attribute WHERE entity_type_id = your_attribute_entity_type_id

Now having found the attribute_id that you need to add, next you need the default option value to set it to, for this use the SQL below.

SELECT eao.option_id, eaov.value_id, eaov.value, eaov.store_id
FROM eav_attribute_option eao
INNER JOIN eav_attribute_option_value eaov
ON eao.option_id = eaov.option_id
WHERE eao.attribute_id  = your_attribute_id

This SQL will give you a list of options that are available to set for the attribute you are looking at, and the value you need is the ‘option_id’ and also the ‘store_id’.

You now have all the information you need to set the attribute in bulk using the SQL below to update the catalog_product_entity tables.

INSERT INTO catalog_product_entity_int (`entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`)
SELECT your_entity_id, your_attribute_id, your_store_id, entity_id, your_option_id
FROM catalog_product_entity
WHERE entity_id not in
(select entity_id from catalog_product_entity_int where attribute_id = your_attribute_id)

To make life easier I have coloured the sections of the above SQL query where you need to insert your ID’s in place of the name holders I have specified.

And dont forget before you go anything as mad as this, please please please backup your database.