sql query to increase/decrease price

Hi there,

I need to run a basic mysql query to increase all the prices in "price field" in my table ( myproducts) by say 10%

the myproducts table contains about few thousand records.

help is much appreciated!

 

Something like this should work.

UPDATE myproducts SET price = price*1.1;

 

Hi jestep, the query works great! thanks

what if I need to decrease the price by -10% later ? may I know the query is?

 

You would do the same but:

UPDATE myproducts SET price = price*.9;

 

Just to be pedantic, if you ran the first query jestep gave you (increase by 10%) then immediately ran the second query he gave you (decrease by 10%) your prices would not end up where they started.

Suppose an item cost $100 initially. After a 10% increase ($100 * .1) it goes to to $110. After a 10% decrease ($110 * .9) it goes to $99.

Those queries will do exactly what you asked for, but may not be what you wanted.

 

Yes, if you want to go back to the original, you need to use a reverse % equation.

Original = Total/(1+%)

This will need to be rounded, but basically you would do:

UPDATE myproducts SET price = ROUND(price/(1+.1),0);