updating values in rows via a specified amount

[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?

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"

[r937]

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

update fruits 
   set Apples  = Apples + 937
     , Oranges = Oranges + 937
     , Kiwi    = Kiwi + 937
 where ID >= 1

[vhogarth]

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.

[vhogarth]

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.

[r937]

add, subtract, square roots, whatever floats your boat