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.