Woocommerce MySQL 更新特定类别产品的价格 [英] Woocommerce MySQL to update price of products in specific category
问题描述
我正在尝试按特定类别中某些产品的百分比提高价格.我想出了应该工作的 select 语句,但是我无法将它与 update 语句结合起来.这是我所拥有的:
I am trying to increase the price by a percentage of certain products that are in a specific category. I figured out the select statement that should work, but am having trouble combining it the update statement. Here is what I have:
SELECT * from `wp_term_relationships` where term_taxonomy_id=376 and object_id in(select ID from `wp_posts` where `post_type`='product' and post_status='publish' and ID=wp_term_relationships.object_id)
这给了我我需要的产品.我可以对这些产品运行更新(如下所示)还是需要以某种方式组合它们?
This gives me the products that I need. Can I run an UPDATE (like below) on those products or do I need to combine them somehow?
update wp_postmeta set meta_value = meta_value * 1.40 where meta_key='_regular_price'
推荐答案
这是一个 MySQL 查询,可以满足您的需求.
Here is a MySQL query which'll serve your purpose.
更新_regular_price
UPDATE
`wp_postmeta`
SET
`meta_value` = ROUND(`meta_value` * 1.40, 2)
WHERE
meta_key = '_regular_price'
AND `post_id` IN (
SELECT
`object_id` AS product_id
FROM
`wp_term_relationships`
WHERE
term_taxonomy_id = 376
AND `object_id` IN (
SELECT
`ID`
FROM
`wp_posts`
WHERE
`post_type` = 'product'
AND `post_status` = 'publish'
AND `ID` = `object_id`
)
);
更新_price
UPDATE
`wp_postmeta`
SET
`meta_value` = ROUND(`meta_value` * 1.40, 2)
WHERE
meta_key = '_price'
AND `post_id` IN (
SELECT
`object_id` AS product_id
FROM
`wp_term_relationships`
WHERE
term_taxonomy_id = 376
AND `object_id` IN (
SELECT
`ID`
FROM
`wp_posts`
WHERE
`post_type` = 'product'
AND `post_status` = 'publish'
AND `ID` = `object_id`
)
);
此外,您还必须删除存储在 _transient_timeout_wc_var_prices_{{post_id}}
下的 wp_options
表中的 WooCommerce 产品价格缓存和_transient_wc_var_prices_{{post_id}}
在 option_name
Also you have to delete WooCommerce product price caching which is stored in wp_options
table under _transient_timeout_wc_var_prices_{{post_id}}
and _transient_wc_var_prices_{{post_id}}
in option_name
DELETE
FROM `wp_options`
WHERE (`option_name` LIKE '_transient_wc_var_prices_%'
OR `option_name` LIKE '_transient_timeout_wc_var_prices_%')
以上查询经过测试,对我有用.
在运行这个查询之前做一个数据库备份
希望这会有所帮助!
这篇关于Woocommerce MySQL 更新特定类别产品的价格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!