1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

sql query to increase/decrease price

Discussion in 'Databases' started by marksz, Jan 19, 2010.

  1. #1
    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%
    SEMrush
    the myproducts table contains about few thousand records.

    help is much appreciated!
     
    marksz, Jan 19, 2010 IP
    SEMrush
  2. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,640
    Likes Received:
    212
    Best Answers:
    18
    Trophy Points:
    330
    #2
    Something like this should work.

    UPDATE myproducts SET price = price*1.1;
     
    jestep, Jan 19, 2010 IP
  3. marksz

    marksz Peon

    Messages:
    36
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Hi jestep, the query works great! thanks

    what if I need to decrease the price by -10% later ? may I know the query is?
     
    marksz, Jan 20, 2010 IP
  4. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,640
    Likes Received:
    212
    Best Answers:
    18
    Trophy Points:
    330
    #4
    You would do the same but:

    UPDATE myproducts SET price = price*.9;
     
    jestep, Jan 21, 2010 IP
  5. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #5
    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.
     
    plog, Jan 21, 2010 IP
  6. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,640
    Likes Received:
    212
    Best Answers:
    18
    Trophy Points:
    330
    #6
    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);
     
    jestep, Jan 21, 2010 IP