Update all WooCommerce product prices to 2 decimal

Question

Many of my products have prices like £3.4560 etc. I need them to rounded and stripped to just 2 in the database.

The output of 2 decimal places in WooCommerce is not enough and fails to work with a Tax Toggle plugin.

Is there a database query that can do this?

I've seen some bits on Rounding and Truncate but not sure how to execute this as my knowledge is poor.

Any help is appreciated.

Answer 1

Update 2: (Added some code to clear all related product transient cache)

Here is some code that will update all the product prices (make a database backup before):

global $wpdb;
$postmeta = $wpdb->prefix . "postmeta";
$posts = $wpdb->prefix . "posts";

// 1. First query: Get all prices
$results = $wpdb->get_results( "
    SELECT $postmeta.*
    FROM $postmeta
    INNER JOIN $posts ON $postmeta.post_id = $posts.ID
    WHERE $posts.post_type LIKE '%product%'
    AND $postmeta.meta_key LIKE '%price%'
    AND $postmeta.meta_value != ''
    ORDER BY $postmeta.meta_id ASC
" );

// iterating through each price and update it
foreach($results as $result){
    $meta_id    = $result->meta_id;
    $post_id    = $result->post_id;
    $meta_key   = $result->meta_key;
    $meta_value = number_format( $result->meta_value, 2 );

    // 2. Udating prices query
    $wpdb->query( $wpdb->prepare( "
        UPDATE $postmeta
        SET meta_id = $meta_id, post_id = $post_id, meta_key = '$meta_key', meta_value = '$meta_value'
        WHERE $postmeta.meta_id = %d
    ", $meta_id ) );

     // 3. Clear all related product transient cached data (refresh prices)
     wc_delete_product_transients($post_id);
}

Code goes in function.php file of your active child theme (or theme) or also in any plugin file.

Once saved, just browse any page of your site, check your database wp_postmeta table searching for ‰price‰ LIKE meta_key. Now you can remove this code.