SQL – Update Price and Special Price Based on Specific Store in Magento

magento-enterprisepricespecial-pricesql

I 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 believe the table I would need to do the query on is the catalog_product_entity_decimal table. However not sure how to write the sql.

So far I have the below query which I believe will get the price of all products within a specific store id. However I am not sure how to add to this query to also get the sku of the item it is getting the price for. 

$storeId = 4;
$sql = "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.store_id = $storeId
        ";

I also tried the below query which I thought might give me the products sku and price

 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

however the second query also does not return anything

Best Answer

Ok so I figured out the answer to my own question :)

For anyone else needing to do this the way is the following: 1. enable "Use Flat Catalog Category" at admin > System > Configuration > CATALOG > Catalog > Frontend 2. run a full re-index via ssh 3. set indexers to Update when scheduled at admin > System > Configuration > ADVANCED > Index Management > Index Management Options 4. in ssh check that crontab is set like below:

          • sh /YOUR_MAGENTO_ROOT_DIRECTORY/cron.sh

Now I could just query the table catalog_product_flat_1 where the #1 represents the store id I want to edit the pricing of a sku for. The catalog_product_flat_1 table will have a sku, price special_price column

Related Topic