SQL to update price and special price based on specific store

[jayreis]

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

[crichardson]

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.