[RESOLVED] Maths in a mySQL query

F_SH

Hey

I'm wondering if it's possible to do one-query updates to numerical data in a mySQL row. Something along the lines of

UPDATE table WHERE condition SET value=value+1;

Effectively what I want to do is increment a value of a row without doing all this:

SELECT * FROM table WHERE condition;

[I]Then use a bloated loop to extract the one value x I want on one row.[/I]

x = x + 1;

UPDATE table WHERE condition SET value=x;

Ultimately, I'm hoping to be able to do this:

UPDATE table WHERE variable=value SET value=value+1;
UPDATE table WHERE variable=value+1 SET value=value-1;

Problem with this 'swap' operation (if you imagine the variable to be an integer order code) is that the second query will affect both rows, I need to execute them concurrently I'm guessing. Can that be done?

Cheers

kubis

UPDATE table WHERE variable=value SET value=value+1;
UPDATE table WHERE variable=value+1 SET value=value-1;

first, it should be:
UPDATE table SET value=value+1 WHERE variable=value ;
UPDATE table SET value=value-1 WHERE variable=value+1;

secondly, i think this is a bad idea;

ï suggest you make following steps (assunming 'variable' is externally added):

1) create a temporary column to table and name it, lets say value_orig
2) run update on the table: UPDATE table SET value_orig=value
3) update the 'value' column: UPDATE table SET value=value+1 WHERE value=variable
4) update the 'value' column: UPDATE table SET value=value-1 WHERE value_orig=variable+1
5) drop the temoporary column value_orig

hopefully it helps

Disclaimer: havent checked whether the logic works correclty 😉 just hope so

Roger_Ramjet

You need to use control flow functions to do this

UPDATE table SET column = 
   CASE column
   WHEN variable THEN column+1
   WHEN variable+1 THEN column-1
   ELSE column END

I am assuming that you want to swap positions in a numbered/ordered list
eg:
17 to 18 and 18 to 17

So inputting the var 17 would mean the row with 17 in column would increment it to 18 and the row with 18 would decrement to 17, all else would stay unchanged

F_SH

That's the kind of elegant solution I was looking for, thanks!

I managed to get a 3-query version working, but it was ugly.