3

Let t be a mysql table and n be an integer column in it. I would be interested if the following query can be modified so that the expression n - 10 is calculated only once.

UPDATE t SET n = if(n - 10 < 1, 1, n - 10) WHERE n = 5;

The query does not make sense, I know, but I need to use the same pattern in the real application where values 10 and 5 would be php variables.

clime
  • 8,695
  • 10
  • 61
  • 82
  • What do you mean by calculated only once exactly? – JohnFx Jan 27 '12 at 02:17
  • Why do you care if it is. It will be millions of times faster that actually writing the result to the DB. – Adrian Cornish Jan 27 '12 at 02:18
  • Well, now it is calculated twice (for every occurrence in the if). I want the n-10 expression to be there just once - in an assignment to a user variable like this: @tmp := n - 10. But I do not know the exact syntax. – clime Jan 27 '12 at 02:24

2 Answers2

4

Allright, i have finally found the right syntax. The parentheses around @tmp := n - 10 are crucial.

UPDATE t SET n = if((@tmp := n - 10) < 1, 1, @tmp) WHERE n = 5;
clime
  • 8,695
  • 10
  • 61
  • 82
-1

You can just do such logic in php:

$value = $n - 10;
$value = $value < 1 ? 1 : $value;
$sql = "UPDATE t SET n = $value WHERE n = $n";
xdazz
  • 158,678
  • 38
  • 247
  • 274
  • 1
    Thank you, but I might have oversimplified my example. In fact, I need to derive the resulting value of n based on other columns in table. – clime Jan 27 '12 at 04:14