我正在尝试将特定类别中某些产品的价格提高一定百分比.我想出了应该起作用的选择语句,但是在将其与更新语句组合时遇到了麻烦.这是我所拥有的:
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)
这给了我所需的产品.我可以在这些产品上运行UPDATE(如下所示),还是需要以某种方式组合它们?
update wp_postmeta set meta_value = meta_value * 1.40 where meta_key='_regular_price'
解决方法:
这是一个MySQL查询,可以满足您的目的.
更新_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`
)
);
另外,您还必须删除存储在option_name中_transient_timeout_wc_var_prices _ {{post_id}}和_transient_wc_var_prices _ {{post_id}}下的wp_options表中的WooCommerce产品价格缓存.
DELETE
FROM `wp_options`
WHERE (`option_name` LIKE '_transient_wc_var_prices_%'
OR `option_name` LIKE '_transient_timeout_wc_var_prices_%')
以上查询已为我测试并工作.
在运行此查询之前,请先进行数据库备份
希望这可以帮助!