user variables in mysql update statement

Question

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.

Why do you care if it is. It will be millions of times faster that actually writing the result to the DB. — Adrian Cornish
– Adrian Cornish, Commented Jan 27, 2012 at 2: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
– clime, Commented Jan 27, 2012 at 2:24

clime · Accepted Answer · 2012-01-27 03:36:16Z

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;

answered Jan 27, 2012 at 3:36

clime

8,94610 gold badges64 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan Rapp Over a year ago

Thanks, I was trying to do this exact thing and adding the parentheses is what made it work.

xdazz · Accepted Answer · 2012-01-27 03:48:32Z

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

answered Jan 27, 2012 at 3:48

xdazz

161k38 gold badges255 silver badges278 bronze badges

1 Comment

clime Over a year ago

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.

Collectives™ on Stack Overflow

user variables in mysql update statement

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related