将 WooCommerce 产品销售价格复制到正常价格并重置销售价格 [英] Copy WooCommerce products Sale prices to regular prices and reset Sale prices

问题描述

我想用销售价格值替换我的 WooCommerce 商店中的所有常规价格，并删除 wp_postmeta 表下的销售价格值.

I Would like to replace all Regular prices on my WooCommerce store with the Sale Price value and delete the Sale Price values under the wp_postmeta table.

post_id 列对于每种产品的价格都相同，要复制的列是 _sale_price 到_price 并删除 _sale_price.

我可以运行什么查询来实现这一点?

What query could I run to achieve this?

推荐答案

这是您正在寻找的纯 MySQL 查询:

Here is a pure MySQL query that you are looking for:

首先你必须使用 self join 来更新 _price 和 _regular_price 字段

First you have to use self join to update _price and _regular_price field

UPDATE `wp_postmeta` AS a,
       `wp_postmeta` AS b
SET a.`meta_value` = b.meta_value
WHERE a.post_id = b.post_id
  AND a.meta_key IN ('_price', '_regular_price')
  AND b.meta_key = '_sale_price'
  AND b.meta_value != ''
  AND a.`post_id` IN
    (SELECT `ID`
     FROM `wp_posts`
     WHERE `post_type` = 'product'
       AND `post_status` = 'publish');

现在要重置销售价格和日期，您必须将其设置为空白

Now to reset sale price and date you have to set it blank

UPDATE `wp_postmeta`
SET `meta_value` = ''
WHERE `meta_key` IN ('_sale_price', '_sale_price_dates_from', '_sale_price_dates_to')
  AND `post_id` IN
    (SELECT `ID`
     FROM `wp_posts`
     WHERE `post_type` = 'product'
       AND `post_status` = 'publish' );

如果您想删除此字段，请使用带有 WHERE 子句的 DELETE 语句，它将解决您的目的.

If you want to delete this fields then use DELETE statement with the WHERE clause, it will solve your purpose.

此外，您还必须删除存储在 _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_%')

特别感谢@LoicTheAztec 指出产品价格缓存

以上查询经过测试，对我有用.

在运行这个查询之前做一个数据库备份

希望这会有所帮助！

这篇关于将 WooCommerce 产品销售价格复制到正常价格并重置销售价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！