Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Consider a simple update as

SET @var1 = 20;
SET @var2 = 26;
    col1=IF(@var1>50, @var1 := @var1-col4, @var1 := @var1+col5),
    col2=IF(@var2>50, @var2 := @var2-col6, @var2);

How we can change two variables in one condition. For example, consider that when @var1<50, we want to change not only @var1 := @var1+col5, but also re-assign @var2 := @var2 + 100. In fact, regardless of the condition 2, we want to increase @var2, if the first condition fails (the second part).

Is it possible to re-assign two user-variable in a IF STATEMENT or we need to add another IF STATEMENT?

This will be equivalent to

SET @var1 = 20;
SET @var2 = 26;
    col1=IF(@var1>50, @var1 := @var1-col4, @var1 := @var1+col5),
    virtual_col=IF(@var1>50, NULL, @var2 := @var2+100),
    col2=IF(@var2>50, @var2 := @var2-col6, @var2);

Here we have created a useless column, not to collect the data, but to repeat the IF STATEMENT for the @var2. Obviously, it is not good from performance point of view, as we are running a useless UPDATE action.

share|improve this question
up vote 2 down vote accepted

It is possible to do two assignments in one IF statement.

There are several ways; I present one, using CASE statement:

SET @var1 = 20;
SET @var2 = 26;
      @var1 := @var1-col4, 
        WHEN @var1 := @var1+col5 IS NULL THEN NULL
        WHEN @var2 := @var2 + 100 IS NULL THEN NULL
        ELSE @var1
col2=IF(@var2>50, @var2 := @var2-col6, @var2);

The idea is to create a CASE statement where first WHEN clauses fail. They fail because we want them to, but along they way they get evaluated.

But in order for them to get evaluated, they must first computer the assignment expression, such as @var1 := @var1+col5 and @var2 := @var2 + 100.

CASE expressions work from first WHEN clause to the next one, and must work in this order.

Please consider reading more about it in my presentation of last year's Percona Live London (2011) conference. For referece, relevant slides are 13 - 15: Programmatic queries: things you can code with sql

share|improve this answer
very subtle solution, and I enjoyed your useful presentation! – All Dec 20 '12 at 20:01
Strangely, this didn't work for me. I went with using a subquery where the ignore columns in the subquery were variable assignments. – 0b10011 Nov 22 '14 at 1:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.