Avatar of Ryan Rood
Ryan Rood
Flag for Canada asked on

Update field with new rounded value

I am working with WordPress and WooCommerce. I am not overly familiar with the database structure but I have the data I want (below). Some of the prices are being calculated to the fourth decimal place (no idea how). I would like to run an update script to force these values to round to two decimal places. I am struggling with the syntax. Any help would be greatly appreciated.

SELECT 
  p.ID,
  MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku,   p.post_title,   `post_content`,   `post_excerpt`,   t.name AS product_category,   t.term_id AS product_id,   t.slug AS product_slug,   tt.term_taxonomy_id AS tt_term_taxonomia,   tr.term_taxonomy_id AS tr_term_taxonomia,   MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,   MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as regular_price,   MAX(CASE WHEN pm1.meta_key = '_sale_price' then pm1.meta_value ELSE NULL END) as sale_price FROM wp_posts p  LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID LEFT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID JOIN wp_term_taxonomy AS tt ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = tr.term_taxonomy_id  JOIN wp_terms AS t ON t.term_id = tt.term_id WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish' AND p.post_content <> '' GROUP BY p.ID,p.post_title

Open in new window


UPDATE x
SET ROUND(field,2)
WHERE x

Open in new window

WordPressMySQL Server

Avatar of undefined
Last Comment
Ryan Rood

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
lenamtl

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ryan Rood

ASKER
Thanks lenamtl. I appreciate the information. I will leave the database as is and work around my issues another way. Hopefully they do fix the issues with the calculations on tax. We have already had to modify how we are calculating tax in the online system compared to our local system.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy