I’m afraid you’ve got a problem with your calculations…
Say we have a product that now, pre-hike costs Â£9.99 inc the 17.5% VAT.
Â£9.99 = 117.5%
Â£8.50 = 100%
Â£1.49 = 17.5%
So the end price is made up of Â£8.50 plus Â£1.49 VAT = Â£9.99
That Â£1.49 part is rising.
20% * 9.99 = 1.998
Now it’s Â£8.50 + Â£1.998 = Â£10.498 - or Â£10.50 after rounding.
So what was Â£9.99 with 17.5% VAT included should now become Â£10.498 with 20% VAT included.
The first bit is correct to find out the original (100%) value, but you go on to add the 20% VAT on to the old value (effectively 117.5% value) instead of the 100% value…
20% * 9.99 = 1.998
..it should be..
20% * 8.50 = 1.70
So the total should be Â£10.20 with 20% VAT, not Â£10.498!
You can check that I’m correct by using the online VAT calculator tool at : http://www.thevatcalculator.co.uk/index.php
Secondly, with your SQL statement you are changing ALL product attributes with the type of DECIMAL, that means you will also be increasing the Weight and Cost attributes for example (other Decimal attributes may have also been added by the user). So really you need the SQL to specifically target the Price attribute only. You can do that with the following SQL…
UPDATE catalog_product_entity_decimal val
SET val.value = (((val.value / 47) * 40) * 1.2)
WHERE val.attribute_id = (
FROM eav_attribute eav
WHERE eav.entity_type_id = 4
AND eav.attribute_code = 'price'
For clarity, that code will increases prices from having 17.5% VAT included to having 20% VAT included.
If you want to make the prices look nice you can change the SET line to…
..which will (always) round up to the near pound.
SET val.value = CEILING(((val.value / 47) * 40) * 1.2)
If you didn’t want your prices to be to the whole pound (say .95 instead) you could change the SET line to…
..which essentially does the same rounding up but then takes 5p off.
SET val.value = CEILING(((val.value / 47) * 40) * 1.2) - 0.05
The above SQL will only change the price, if you need to change the special price as well you just need to change the attribute_code line…
.. and re-run.
AND eav.attribute_code = 'special_price'
As always when hitting the DB directly like this it would be best to work on a test copy first to ensure it works as expected, then also make a backup before running on live!