Copy WooCommerce products Sale prices to regular prices and reset Sale prices

Question

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?

Answer 1

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!

Answer 2

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:

• the active price: '_price'
• the regular price: '_regular_price'
• the sale 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:

• Starting Updating prices (step one):

• Continuing necessary steps:

• Updating prices done (end screen):

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.