Simple Math – Updating MySQL Prices by a Percentage (And rounding)

The other day we had the simple request from a client – they asked us to update every price on their site. They asked us to increase their prices on ALL their products by 8%.

This is a simple bit of SQL which updates all the numbers in a field by a percent.

Let’s say our database is called catalog.

Our table is called products.

Our field is called products_price.

So the query on the catalog database would be:

UPDATE products SET products_price = products_price * 1.08

Simple! ‘UPDATE products’ tells the database which table we’re talking about, and ‘SET products_price’ will alter every value in the table in the ‘products_price’ field.

Then, we say ‘products_price = products_price * 1.08’, which simply says that the each value for products_price will be 1.08 times the current value. As we know, a value * 1.08 is 8% bigger.

But wait! That’s given us some nasty prices – for example,

523.99 * 1.08 = 565.9092

4 decimal places? We could use the ROUND function to round our values to 2 decimal places.

UPDATE products SET products_price = ROUND ( products_price , 2 )

I’ve kept it in the same format as above so you can see how it is used. This will round all values to 2 decimal places. Now, to run both queries at the same time:

UPDATE products SET products_price = ROUND ( products_price * 1.08 , 2 )

Easy! That’s updated all your prices in a couple of seconds. This was run on a site using ZenCart – that’s where the database structure is from, while the query was run in phpMyAdmin.