How to Decrease prices by 5% via SQL statement on all products ?

Hello -

I wanted to know how to decrease or increase prices by 5% via sql statement.


UPDATE cscart_product_prices SET price = ROUND(price/1.05,2)

This will decrease your prices by 5% and round the figure to a whole number (eg. £99.99 instead of £99.99123). Note 'cscart_product_prices' is the schema for V2, V3 and V4.

Note you must use ROUND to 2 decimal places, otherwise doing the reverse (“price*1.05,2”) will not restore your prices to the original prices.

multiply the price by 0.95 to reduce the price:

UPDATE cscart_product_prices SET price = price * .95

TEST RESULTS FIRST with one product:

UPDATE cscart_product_prices SET price = price * .95 WHERE product_id = 'ANY ONE PRODUCT ID'

You could alternately update the table with a percentage_discount value of 5, which would preserve the original price but reduce all the prices by 5% - that method would make it easier to go back to the original price (or to tweak the discount to other values):

UPDATE cscart_product_prices SET percentage_discount = '5'

Sorry, I only use whole numbers for pricing. What Stellar says about rounding is true - we cross-posted.