Round numbers in database to two decimal places

Other Tech Topics
1

I want to round the numbers to 2 decimal points in a woocommerce database table wp_postmeta.
There is a column 'meta_key' with row '_price' and a column 'meta_value' which has all kinds of data in it as well as the numbers that need to be rounded and reduced to two decimal places.
(I know I'll also have to do that for_regular_price)

I am a complete beginner with SQL. I've managed to do a few things in phpmyadmin by using the Search to give me the syntax or copying and changing answers found on the net, but this one has me stumped, just not enough knowledge yet.

I managed to add 10% to all the prices with this query -
update wp_postmeta set meta_value = meta_value * 1.10 where meta_key='_regular_price'

I've read that there's a 'ROUND' function, but not sure how to write the query.

I'm guessing it would be something like this -
UPDATE wp_postmeta ROUND meta_value where meta_key='_price'

Hope someone here can help

Rob

2

can you give this a try

UPDATE wp_postmeta set meta_value=convert(decimal(5,2),round(meta_value))
where meta_key='_price'

3

Thanks for the quick reply- unfortunately I get an error message "
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'decimal(5,2),round(meta_value))
where meta_key='_price'' at line 1"

4

That's MYSQL.
I have given you the MSSQL query.
may be you should try posting on a MYSQL forum.

5

I'm very sorry, I know so little I thought SQL was SQL - yes I'm using MySQL.
So SQLTeam is totally the wrong forum for my question ?

6

There are a few people here who are experts in multiple database technologies.
But SQL Team is for MSSQL.

7

MySQL used to have extensive documentation that would help you lot with such things. It used to be on the MySQL site. Dunno if all that is still there... I haven't looked at it since re 4.11.