20

Is it possible to get the current value of a field, use it as a variable in a calculation, then update the field based on the result?

For example the record with the ID "1" in table1 has a value of "2"

SELECT table1
WHERE ID = "1"
SET RESULT to CurrentID
RESULT = CurrentID + 1;
Spontifixus
  • 6,570
  • 9
  • 45
  • 63
blunders
  • 3,619
  • 10
  • 43
  • 65

2 Answers2

22

This will set the result column to the value of the CurrentID column (plus 1) of the same row, for any row that has an ID column that equals "1":

UPDATE table1
SET result = CurrentID + 1
WHERE ID = "1"
Oded
  • 489,969
  • 99
  • 883
  • 1,009
7

You might try 

Update table1 set ID = ID+1 where ID = 1
John K.
  • 5,426
  • 1
  • 21
  • 20
  • Why would he not just do `SET ID=2 WHERE ID=1`? – JNK Apr 14 '11 at 19:01
  • I guess what I'm saying is, if he's only doing one `ID` at a time, then he can set an explicit value and bypass the calculation – JNK Apr 14 '11 at 19:04
  • @JNK: No I'm not doing one value at a time, my example code was pretty poor -- wasn't even sure if this was possible so I was trying to keep the code as simple as possible. John K. code appears to work, though I'm not able to get Oded's to work. – blunders Apr 14 '11 at 19:14
  • Right... I've been looking back at the question. Not quite sure why he asked it like that, but I suppose there's more that he'd like to do based on the question...I added on the where clause based on his example. – John K. Apr 14 '11 at 19:14