Search code examples
mysqlsql-updateuser-variables

user variables in mysql update statement

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.

Solution

  • 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;