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.
The column post_id is identical for both prices for each product and the column to copy is _sale_price to _price and delete _sale_price.
What query could I run to achieve this?
Here is a pure MySQL query that you are looking for:
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' );
If you want to delete this fields then use DELETE statement with the WHERE clause, it will solve your purpose.
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_%')
A special thanks to @LoicTheAztec for pointing out the product price caching
Above query is tested and worked for me.
Before running this query do take a database backup
Hope this helps!
Here is a custom function that will copy the sale price to the regular price and will reset the sale price.Regarding the prices in WooCommerce there is:
'_price''_regular_price''_sale_price'So you need to update the regular price too and to reset all related data for the sale price. Also you need to refresh prices.
This function has an UI that will appear in shop pages or single product pages only for Admins and Shop Manager.
If you have more than 1000 products, the process will be auto split in multiple steps (by 1000 products) to avoid errors when there is too many products.
The SQL query will only select products Ids that have a sale price and not all products. To update/Reset prices I use update_post_meta() function instead of a query, to refresh each product cache...
It looks like:
Important: Before starting this kind of process always make a database backup
Here is that hooked function code:
add_action( 'woocommerce_before_main_content', function(){
// Only admins and shop manargers
if( ! current_user_can( 'edit_products' ) ) return;
global $wpdb;
$products_count = get_option( 'product_prices_update_count' );
// Auto enable multistep price updates for more than 1000 products
$limit = $products_count > 2 ? 2 : false;
if( $limit != false )
$offset = get_option( 'product_prices_update_offset' );
if( empty( $offset ) && $limit != false ) {
$offset = 0;
add_option( 'product_prices_update_offset', $offset );
}
$control_process = $limit != false ? "LIMIT $offset, $limit" : "";
if( ! isset( $_POST['prices_updates'] ) ) $control_process = '';
// 1. First query: Get for all product ids the sale price
$results = $wpdb->get_results( "
SELECT postmeta.post_id, postmeta.meta_value as price
FROM {$wpdb->prefix}postmeta as postmeta
INNER JOIN {$wpdb->prefix}posts as posts ON postmeta.post_id = posts.ID
WHERE posts.post_type LIKE '%product%'
AND postmeta.meta_key = '_sale_price'
AND postmeta.meta_value != ''
ORDER BY posts.ID ASC
$control_process
" );
if( empty( $products_count ) ){
update_option( 'product_prices_update_count', count($results) );
$count = count($results);
} else $count = $products_count;
$remaining = ! empty( $products_count ) && $limit != false ? $count-($offset+$limit) : count($results);
$products_updated = 0;
echo '<div style="border: solid 1px grey; padding: 16px; margin-bottom: 12px;)">
<form class="cart" method="post" enctype="multipart/form-data">';
if( isset( $_POST['prices_updates'] ) && ( $remaining > 0 || ! $limit ) ){
foreach($results as $result){
$post_id = $result->post_id;
$price = $result->price;
// 2. Updating Active and Regular prices
update_post_meta( $post_id, '_price', $price );
update_post_meta( $post_id, '_regular_price', $price );
// 3. Reset Sale price
update_post_meta( $post_id, '_sale_price', '' );
update_post_meta( $post_id, '_sale_price_dates_from', '');
update_post_meta( $post_id, '_sale_price_dates_to', '');
// 4. Refresh product cache
wc_delete_product_transients($post_id);
$products_updated++;
// echo "$post_id ($products_updated), ";
}
}
if( ( ! $limit && ! empty( $products_count ) ) || $remaining <= 0 ){
echo "<p><strong>Process is now finished.</strong><br>
<em>You can remove or comment the code, Thanks</em></p>";
if( isset($_POST['prices_updates'] ) ){
delete_option( 'product_prices_update_offset' );
delete_option( 'product_prices_update_count' );
}
} else {
if( isset( $_POST['prices_updates'] ) ){
update_option( 'product_prices_update_offset', $offset+$limit );
echo "<p>$products_updated products have been updated.<br>";
echo "There is still $remaining remaining products to update.</p>";
} else
echo "<p>There is $remaining products to update.</p>";
$value = empty( $products_count ) ? "Start update" : "Continue update";
echo '<input type="submit" class="button" name="prices_updates" value="'.$value.'" />';
}
echo '</form></div>';
}, 2, 0);
Code goes in function.php file of your active child theme (or theme) or also in any plugin file.
This code is tested and works.