cancel
Showing results for 
Search instead for 
Did you mean: 

SQL to update price and special price based on specific store

SQL to update price and special price based on specific store

have a Magento Enterprise 1.14.2.1 multi-site and what I want is lets say I have two store views setup store "store_US" and "store_AU"

I want to run a php file that would get each sku, price and special price from the "store_US" and then multiple the price used in the store_us and save the new value to the price in the "store_au" for that sku. and then the same for the special price value of each sku. I want to do this because I don't want to just have a regular conversion of USD to AU price and instead want a specific price markup from usd to AUD

So I was thinking I would need a query sort of like the below which yes I know is not a valid mysql query but I am looking for help on how to write the below query that would get all prices and sku's from a specific store id

 

select value from catalog_product_entity_decimal val 
        where val.attribute_id = 
            (
                SELECT attribute_id FROM eav_attribute eav
                WHERE eav.entity_type_id = 4 
                AND eav.attribute_code = 'price'
            )
        AND val.attribute_id = 
            (
                SELECT attribute_id FROM eav_attribute eav
                WHERE eav.entity_type_id = 4 
                AND eav.attribute_code = 'sku'
            )
        AND val.store_id = 4
1 REPLY 1

Re: SQL to update price and special price based on specific store

So the first thing you will want to do is call getSelect() on your collection statement to get the valid sql for the call so something like

 

$sql = mage::getModel('catalog/product')->getCollection()->setStoreId($storeId)->getSelect();

var_dump($sql);

 

To actually run queries you will have pull in the core/read and core/write models just google around about how to execute sql in magento.

 

 

wow didnt see how old this was ...oh well already wrote it down.