Magento bulk price changes

Question

What I'm trying to do is, a bulk price change on products in a certain category. We receive suggested retail prices from our vendor, but sometimes these don't work for us. So we need to take the cost price of the product, and for example, add 20% to the product so.. easy enough cost = cost + 0.2*cost. Now I need to do this on all the products in the selected category, so here is what I have thus far...

$category = Mage::getModel('catalog/category')->load(189); 

// load products from category id '189'
$products   = Mage::getModel('catalog/product')
                ->getCollection()  
                ->addCategoryFilter($category)  
                ->addAttributeToSelect('id'); 

foreach($products as $product) { 
    // get the current cost of the product
    $cost = $db->fetchRow("SELECT value FROM `m_catalog_product_entity_decimal` WHERE entity_id='" . $product->getId() . "' AND attribute_id='68'");
    $cost = $cost['value']; 
    $newCost = $cost + $cost*$percentage; 

    // update the product with a new cost
    $db->query("UPDATE `m_catalog_product_entity_decimal` SET value='$newCost' WHERE entity_id='" . $product->getId() . "' AND attribute_id='64'"); 
}

Now, I need to use raw SQL because my php server can't handle all of the magento product loading and saving (Magento 1.4 has a memory leak in the product model). This is why I'm simply selecting the "id" from the product, to get the very least amount of data. I also understand that doing all of these SQL queries is a waste of resources, and thats why I'm here. If each of my categories only had say, 10 products, I would use the product model to update the cost and save the products, but I have sometimes up to 500 or more products in a each category as one time.

I'm hoping to condense this to one SQL query, and get rid of the foreach loop and the product collection. The cost attribute id is 68, and the price attribute id is 64. Any help on this would be much appreciated.

EDIT
Magento uses an EAV model for their database. So for the attributes I need to access, which are "cost" and "price", they are both located in m_catalog_product_entity_decimal

So a products price attribute would look like this in the table

value_id    entity_type_id  attribute_id    store_id    entity_id   value
6401             4              64             0            2184      399.9500

The value_id is just the rows unique value, entity_type_id 4 means this is a product attribute. The attribute_id is associated with the actual attribute. In this case, 64 is the attribute_id for "price". Store_id is irrelevant. Entity_id is the actual product id, and the value is the actual price of the item.

Answer 1

Honestly, B00MER has the right idea, but I've also had to do similar things in a pinch before. Plus, I felt like writing a little SQL. If you just want to multiply costs by 1.2 for all products in a category, you can do something like this:

update catalog_product_entity_decimal
  set value = value*1.2
  where attribute_id = 75 and
        entity_id IN (select product_id from catalog_category_product
                    where category_id = X);

You'll doubtless need to reindex everything and check your results to make sure. Obviously, replace X with your target category, and it appears that you use a table prefix of m_, so append that too (I'm leaving it off for other searchers who come here).

Hope that helps!

Thanks, Joe