?
修改價格為原來的0.6154倍！！
執行的語句
update catalog_product_index_price set price = round(price*0.6154),final_price = round(final_price*0.6154),min_price = round(min_price*0.6154),max_price = round(max_price*0.6154)
UPDATE catalog_product_entity_decimal val SET? val.value = round(val.value* 0.6154)? WHERE? val.attribute_id = 64
?
?
?
?
附：資料
http://fishpig.co.uk/magento-update-product-prices-globaly/
?
?
Magento: Update Product Prices?Globaly
Posted on the 7th of June, 2010 at 12:00pm
There are many ways to mass update product attributes in Magento, each well suited to a different purpose. Magento’s built-in mass product attribute updater is great if you want to modify a selection of products or the new attribute value is the same for all products you want to edit. Alternatively, if you wish to alter the attributes in more dynamic ways, updating them programmatic ally via PHP is probably a better way. The downside to both of these methods is speed, with each product update taking a few seconds to complete. While this time can be dramatically reduced by disabling indexing, the wait can still be too long for a store with a massive catalog. A more efficient way to update product attributes is to write direct SQL queries. As an example, I will show you how to mass update product pricing for all products, products from a certain store and products that use a certain attribute set.
Why would I want to mass update price?
When I was first asked to do this I asked myself the same question, however, the reason is quite simple. In Magento, shipping costs aren’t usually displayed to the user until they enter their delivery address. While this makes sense, the customer usually enters their delivery address during the checkout process, meaning a lot of customers weren’t aware of this extra cost. During a study of one site, I found that almost 30% of customers were leaving the store during checkout and that this bounce rate could almost definitely be attributes to the shipping cost. To remove this problem, it was decided I should add ￡6 (the shipping cost) on to every product price and offer free shipping instead. As soon as this was done a lot less people left the site during checkout!
How do I update product price globally?
In this first example, I will add ￡6 to every single product price.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
|
<?php
?
$priceToAdd
= 6;
?
$write
= Mage::getSingleton(
'core/resource'
)->getConnection(
'core_write'
);
$write
->query("
??
UPDATE catalog_product_entity_decimal val
??
SET? val.value = (val.value +
$priceToAdd
)
??
WHERE? val.attribute_id = (
?????
SELECT attribute_id FROM eav_attribute eav
?????
WHERE eav.entity_type_id = 4
???????
AND eav.attribute_code =
'price'
????
)
");
|
If you have a development site, add the code to a template file or run Magento’s code in an external PHP file and all of your products should now cost ￡6 more.
How do I update all prices from a certain store?
This technique is useful when working in a multi-store Magento environment. The SQL query used is very similar, except you will need to add a clause in the WHERE section to limit the records updated by store ID.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
|
<?php
?
$priceToAdd
= 6;
$storeId
= 4;
?
$write
= Mage::getSingleton(
'core/resource'
)->getConnection(
'core_write'
);
$write
->query("
??
UPDATE catalog_product_entity_decimal val
??
SET? val.value = (val.value +
$priceToAdd
)
??
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
");
|
How do I update all product prices with a certain attribute set?
The concept behind this is the same, however you will need to join an extra table so that you can filter using attribute_set_id.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
|
<?php
?
$priceToAdd
= 6;
$attributeSetId
= 4;
?
$write
= Mage::getSingleton(
'core/resource'
)->getConnection(
'core_write'
);
$write
->query("
??
UPDATE catalog_product_entity_decimal val
??
SET? val.value = (val.value +
$priceToAdd
)
??
WHERE? val.attribute_id = (
?????
SELECT attribute_id FROM eav_attribute eav
?????
WHERE eav.entity_type_id = 4
???????
AND eav.attribute_code =
'price'
????
)
AND entity_id = (
???
SELECT p.entity_id FROM catalog_product_entity p
???
WHERE p.attribute_set_id =
$attributeSetId
)
");
|
How do I update the Special Price?
This one is also extremely easy! If you take any of the above examples and swap ‘price’ for ‘special_price’ they will all work! See below for an example of how to update the special price for every product.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
|
<?php
?
$priceToAdd
= 6;
?
$write
= Mage::getSingleton(
'core/resource'
)->getConnection(
'core_write'
);
$write
->query("
??
UPDATE catalog_product_entity_decimal val
??
SET? val.value = (val.value +
$priceToAdd
)
??
WHERE? val.attribute_id = (
?????
SELECT attribute_id FROM eav_attribute eav
?????
WHERE eav.entity_type_id = 4
???????
AND eav.attribute_code =
'special_price'
????
)
");
|
These features only scratch the surface of the Magento database but should hopefully give you an insight into the possibility of modifying data directly in the database. This method is much quicker than the alternatives, however can go drastically wrong extremely easily. I would make sure you test ALL queries on a development server and always back up your live server before running a query!
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
