MySQL Query Advice

By JeffC - April 5, 2014

I would like to update prices on my website. All prices need to increase by a uniform 10% so rather than updating products individually (there are 2,000 of them) I thought I may be able to execute a query that does them all.

I'm thinking:

UPDATE cms_products SET price= (price *1.1)

Is this correct?

Jeff

By Chris - April 7, 2014

Hi Jeffncou,

That looks correct, yes.

If you want to be extra safe, I'd add an extra field, maybe called "old_price", then before you do your *1.1 update, copy the old prices:

UPDATE cms_products SET old_price = price

This way, if something unexpected happens, you can just copy them back.

You can also use the Database Backup feature in Admin > General Settings to backup and restore just your Products table.

Hope this helps!

All the best,
Chris

By Chris - April 24, 2014

Hi Jeffncou,

You can use the Mysql ROUND() function to do this, giving it the number of decimal places you want the number rounded to (2):

UPDATE cms_products SET price = ROUND(price * 1.1, 2)

Does that help?

All the best,
Chris

By JeffC - April 25, 2014

Hi Chris

That helped a lot, thanks. It hasn't output all of the values perfectly because it doesn't force two decimal places if they are not needed.

For example £7.50 displays as £7.5

For the shopping cart to work correctly it doesn't matter, but visually £7.50 would be better.

It took an hour to manually adjust any issues but it would have taken two days to change all of the prices without the MySql query - so happy days :)

Thanks for your help 

Jeff

By Chris - April 25, 2014

I'd recommend leaving the numbers as-is in your database, and add the extra zeros in with PHP anywhere you want to display prices.

<?php echo number_format($product['price'], 2) ?>

Note that number_format(), by default, will also add commas to numbers greater than 999.

Hope this helps!

All the best,
Chris