For times, we are required to update Magento data in bulk. Unluckily, there is no good bulk update tool provided by Magento unless purchasing third party extension.
Instead of relying on others, we use our programming skill and do the update directly on database using SQL statement. This is more flexible and we can even set cronjob to do things regularly.
To update database using SQL, it’s important firstly to understand the data structure of Magento.
Magento is a relational data relationship but it is using an EAV model. Below are examples. The values are not fixed and depends on your individual implementation. Look it up from database.
| attribute_code | attribute_id | attribute_type_id |
| price, special_price, tier_price | 75, 76, 91 | 4 |
| product name, url key, description, short_description, meta_description | 71, 97, 72, 73,84 | 4 |
| category name | 41 | 3 |
And EAV design different tables for different data type.
| field | eav type |
| meta_description, name | varchar |
| description, short_description | text |