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_codeattribute_idattribute_type_id
price, special_price, tier_price75, 76, 914
product name, url key, description, short_description, meta_description71, 97, 72, 73,844
category name413

And EAV design different tables for different data type.

fieldeav type
meta_description, namevarchar
description, short_descriptiontext