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

General General Questions
1

Hello -



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



Thanks

2


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.

3

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'

4

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