MySQL Help Needed - Round Values

Hi

I need a little bit of help with some MySQL.

I have used the following SQL for my OSCommerce database, because VAT (Tax) recently changed from 17.5% to 20%:

UPDATE `products` set `products_price` = ROUND((products_price/1.175)*1.2, 2);

Code (markup):

This did everything perfectly and raised the prices by the necessary 2.5%. However, instead of having prices like £19.99, I now have £20.42.

I would like to use SQL (due to having 4000 products, updating manually is impractical), to round prices up to the nearest £0.49 or nearest £0.99.

For example, if the prices are as follows, I want them to round to the latter figure.

£10.02 > £10.49
£20.35 > £10.49
£30.50 > £30.99
£40.98 > £40.98

How can I do this with mySQL, can it be done?


Thanks for your help people.

 

Is it £40.98 > £40.98 or £40.98 > £40.99?

 

This one is tricky, I hope you have mysql 5 to run this sql.

Here's the logic:
Round your results (products_price/1.175*1.2) to whole numbers.
If the result was rounded down, then add 0.49 from the result.
If the result was rounded up, then subtract 0.01 from the result to get xx.99.

UPDATE `products` SET `products_price` = 
[INDENT]CASE 
[INDENT]WHEN ROUND(products_price/1.175*1.2) <= products_price/1.175*1.2 
THEN ROUND(products_price/1.175*1.2) + 0.49
WHEN ROUND(products_price/1.175*1.2) >  products_price/1.175*1.2 
THEN ROUND(products_price/1.175*1.2) - 0.01 
[/INDENT]END;
[/INDENT]

Code (markup):

 

Sorry, this should have been £40.98 > £40.99

Thanks for your help - I have noticed that, because some products must include VAT and some are VAT free (for example, books), I need to add a 'WHERE' clause on, such as:

UPDATE `products` SET `products_price` = 
[INDENT]CASE 
[INDENT]WHEN ROUND(products_price/1.175*1.2) <= products_price/1.175*1.2 
THEN ROUND(products_price/1.175*1.2) + 0.49
WHEN ROUND(products_price/1.175*1.2) >  products_price/1.175*1.2 
THEN ROUND(products_price/1.175*1.2) - 0.01
WHERE taxenabled = '1'
[/INDENT]END;
[/INDENT]

Code (markup):

However, this throws up an error, it runs fine without having the WHERE clause. Is there something I'm missing? Thnx

 

Put the where outside of the case statement like this:

UPDATE `products` SET `products_price` = 
[INDENT]CASE 
[INDENT]WHEN ROUND(products_price/1.175*1.2) <= products_price/1.175*1.2 
THEN ROUND(products_price/1.175*1.2) + 0.49
WHEN ROUND(products_price/1.175*1.2) >  products_price/1.175*1.2 
THEN ROUND(products_price/1.175*1.2) - 0.01[/INDENT]
END[/INDENT]
WHERE taxenabled = '1';

Code (markup):