SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist vhogarth's Avatar
    Join Date
    Nov 2003
    Location
    Taxachussets
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    updating values in rows via a specified amount

    Is it possible to update values stored in a row by a certain increment without first querying the values and then adding it to the variable?

    For example:

    Lets say I have a table with fruits with 3 columns

    table: fruits

    column1 ID, column2 Apples, column3 Oranges, column4 Kiwi
    1 3 2 4

    If i want to update the values of apples, oranges, and kiwi by 2 how would i write a sql statement to reflect that?

    "UPDATE fruits SET Apples='+2', Oranges='+2', Kiwi='+2' WHERE ID >=1"

    i'm not really sure the syntax for this kind of operation or whether its even valid. Any ideas?

    What I'd really like is to update the value with a variable. SOmething like this:

    "UPDATE fruits SET Apples='+$x', Oranges='+$x', Kiwi='+$x' WHERE ID >=1"

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vhogarth
    Is it possible to update values stored in a row by a certain increment without first querying the values and then adding it to the variable?
    yes
    Code:
    update fruits 
       set Apples  = Apples + 937
         , Oranges = Oranges + 937
         , Kiwi    = Kiwi + 937
     where ID >= 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist vhogarth's Avatar
    Join Date
    Nov 2003
    Location
    Taxachussets
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937, u are truly a DB guru, you've helped me a few times in the past and i'm thoroughly grateful for it.

  4. #4
    SitePoint Evangelist vhogarth's Avatar
    Join Date
    Nov 2003
    Location
    Taxachussets
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    will it work with subtraction

    Just making sure, will that work with subtraction as well?

    all i'd do would be:

    apples=apples - $x

    I'm assuming if you can add, you can subtract too.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    add, subtract, square roots, whatever floats your boat

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •