MikeF MikeF - 1 year ago 87
PHP Question

Update all WooCommerce products if "sale_price" is greater than "price"

I am trying to update wp_postmeta table for all products sale price.

I'm struggling with how to work this, due to both fields being meta_key/meta_value pairs in this wp_postmeta table.

How can I write a query that will update all

'_sale_price' = 0
'_sale_price' > '_price'


Answer Source

A different approach with a custom function that should do the same job. You will have to use this function just one time, and then remove it after the job is done (at first site load, the process will depend on number of products you have).

Here is the code:

function update_products_sale_price(){

    $args = array(
        'posts_per_page' => -1,
        'post_type' => 'product',
        'post_status' => 'publish'

    // getting all products
    $products = get_posts( $args );

    // Going through all products
    foreach ( $products as $key => $value ) {

        // the product ID
        $product_id = $value->ID;

        // Getting the product sale price
        $sale_price = get_post_meta($product_id, '_sale_price', true);

        // if product sale price is not defined we give to the variable a 0 value
        if (empty($sale_price))
            $sale_price = 0;

        // Getting the product sale price
        $price = get_post_meta($product_id, '_price', true);

        // udate sale_price to 0 if sale price is bigger than price
        if ($sale_price > $price)
            update_post_meta($product_id, '_sale_price', '0');

// Here the function we will do the job.

You can also embed the code function in a hook…

This code goes on function.php file of your active child theme or theme

This code is tested and fully functional