MySQL attribute values auto increment count values of 1

Question

I require to bulk update a set of attribute values in magento. After some struggle i have come up with the following which updates my attribute values starting with 400100 adding +1 for the next and so on.

I run this in phpmyadmin

SET @i := 400100;
UPDATE catalog_product_entity_varchar
SET value = @i:=@i+1
WHERE attribute_id = 136 AND value !='' ;

After running the above this is partially working but i still have thousands of empty values.

Would anyone have any idea where i am going wrong?

Thanks!

Answer 1

I don't know why your code didn't work, but I can give you a alternative that does not involve sql queries. Doing sql queries directly on Magento is very dangerous.
most probably you still have empty values because your query has where value !=''. So your query skips the empty values.
Let's say your attribute code is custom_attribute. First you need to identify your products.

$collection = Mage::getModel('catalog/product')->getCollection();
//add filters to the collection here.
//for example if you still want all the products for which the attribute custom_attribute is not empty you can uncomment the line below
//$collection->addAttributeToFilter('custom_attribute', array('neq'=>''));

Now loop through the collection and update the attribute with your desired values:

$index = 400100;
foreach ($collection as $product) {
    Mage::getSingleton('catalog/product_action')->updateAttributes(
        array($product->getId()), //products to update
        array('custom_attribute'=>$index), //attributes to update
        0 //store view id
    );
    $index++;
}

When you are done clear the cache and reindex everything.