Magento Bulk update attributes

Question

I am missing the SQL out of this to Bulk update attributes by SKU/UPC.

Running EE1.10 FYI

I have all the rest of the code working but I"m not sure the who/what/why of actually updating our attributes, and haven't been able to find them, my logic is

1. Open a CSV and grab all skus and associated attrib into a 2d array
2. Parse the SKU into an entity_id
3. Take the entity_id and the attribute and run updates until finished
4. Take the rest of the day of since its Friday

Here's my (almost finished) code, I would GREATLY appreciate some help.

    /**
     * FUNCTION: updateAttrib
     * 
     * REQS: $db_magento
     * Session resource
     * 
     * REQS: entity_id
     * Product entity value
     * 
     * REQS: $attrib
     * Attribute to alter
     * 
     */

See my response for working production code. Hope this helps someone in the Magento community.

Answer 1

While this may technically work, the code you have written is just about the last way you should do this.

In Magento, you really should be using the models provided by the code and not write database queries on your own.

In your case, if you need to update attributes for 1 or many products, there is a way for you to do that very quickly (and pretty safely).

If you look in: /app/code/core/Mage/Adminhtml/controllers/Catalog/Product/Action/AttributeController.php you will find that this controller is dedicated to updating multiple products quickly.

If you look in the saveAction() function you will find the following line of code:

Mage::getSingleton('catalog/product_action')
    ->updateAttributes($this->_getHelper()->getProductIds(), $attributesData, $storeId);

This code is responsible for updating all the product IDs you want, only the changed attributes for any single store at a time.

The first parameter is basically an array of Product IDs. If you only want to update a single product, just put it in an array.

The second parameter is an array that contains the attributes you want to update for the given products. For example if you wanted to update price to $10 and weight to 5, you would pass the following array:

array('price' => 10.00, 'weight' => 5)

Then finally, the third and final attribute is the store ID you want these updates to happen to. Most likely this number will either be 1 or 0.

I would play around with this function call and use this instead of writing and maintaining your own database queries.

Answer 2

General Update Query will be like:

UPDATE 
  catalog_product_entity_[backend_type] cpex 
SET
  cpex.value = ? 
WHERE cpex.attribute_id = ? 
  AND cpex.entity_id = ?

In order to find the [backend_type] associated with the attribute:

SELECT 
  backend_type
FROM
  eav_attribute
WHERE entity_type_id =
  (SELECT
    entity_type_id
  FROM
    eav_entity_type
  WHERE entity_type_code = 'catalog_product')
AND attribute_id = ?

You can get more info from the following blog article:
http://www.blog.magepsycho.com/magento-eav-structure-role-of-eav_attributes-backend_type-field/

Hope this helps you.