Update all your prices in one go to 1 cent under the dollar

By | October 19, 2012

A customer of mine wanted to update all his prices to 1 cent under the dollar, eg go from 15.80 to 15.99. With 1000s of products that’s an utter waste of time, so here is one line of SQL to use in phpMyadmin;

UPDATE `products` SET `products_price` = (ceil(`products_price`)-0.01);

What this does is set the price to the next nearest whole number minus 1 cent.


3 thoughts on “Update all your prices in one go to 1 cent under the dollar

  1. Isa

    Hi Gary

    This got me thinking…sorry if this if slightly off topic, but would there be a way to convert from USD (default currency) to Swiss Francs whilst rounding up to 0.05?

    I explain:
    1USD = 0.92CHF, but in Swiss Francs 2 centimes (pennies) don’t exist, the minimum is 5.
    So 1USD would have to be rounded up to either 0.95 OR 0.90 Swiss Francs.

    Is there a way to do that?

    P.S: Aren’t we Swiss complete trouble makers??? ;D

  2. Gary Post author

    Hi Isa, that’s not possible in standard osC as the prices are worked out by the automatic currency conversion, eg 1 British pound sterling = 1.2275 euros = 1.4885 Swiss francs.

    Instead we would need to remove the functionality and instead set prices per product, so you have price input box per currency (on each product). Now you can say (for example); 1gbp = 1.23 euro = 1.5chf

    There is an old addon that does this (but is coded quite horribly), so really needs an update for use with 2.3.3.

    PS. Yes 😉

  3. Isa

    Hi Gary – thanks for shedding light on this issue.

    I’ll throw that on my list of things to do (which is growing and growing and growing!!! lol)

Leave a Reply

Your email address will not be published.