Mysql – How to re-assign two variables in a conditional UPDATE

MySQLupdate

Consider a simple update as

SET @var1 = 20;
SET @var2 = 26;
UPDATE table1 SET
    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;
UPDATE table1 SET
    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.

Best Answer

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;
UPDATE table1 SET
    col1=IF(
      @var1>50, 
      @var1 := @var1-col4, 
      CASE
        WHEN @var1 := @var1+col5 IS NULL THEN NULL
        WHEN @var2 := @var2 + 100 IS NULL THEN NULL
        ELSE @var1
      END
    ),
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

Related Solutions

Mysql – How to improve this nested MySQL query to avoid redundant WHERE statement

Reformulating your query:

SELECT 
    numopens / numsends AS Open_Rate,
    numactions / numsends AS Action_Rate
FROM
    ( SELECT 
          ( SELECT COUNT(DISTINCT s.userID) 
            FROM tbl_sends AS s
            WHERE s.mailing_id = param.mailing_id
          )  
        AS numsends,
          ( SELECT COUNT(DISTINCT o.userID) 
            FROM tbl_opens AS o
            WHERE o.mailing_id = param.mailing_id
          )  
        AS numopens
          ( SELECT COUNT(DISTINCT a.userID) 
            FROM tbl_actions AS a 
            WHERE a.mailing_id = param.mailing_id
          )
        AS numactions
      FROM 
          ( SELECT 
                5694     AS mailing_id,
                417      AS another_parameter,
                'funny'  AS one_more_parameter   
          ) AS param  
    ) AS cte ;

Mysql – How to do two actions in conditional UPDATE

If mysql had window functions, it would be quite easy. However, it doesn't, and even though there are quite a few solutions that use variables, most of them (at least the ones I read) warn about potential problems due to the changing order of evaluation when it comes to emulation PARTITION BY part.

My solution doesn't use variables at all (I know, performance is gonna be terrible for big amount of data, but it seems to have no side effects). I hope it can be somehow useful :

I split the task into 2 parts , 1st - creating select statement that returns required rank/rownumber, and 2nd - update which is trivial (simple update ... join) .

(Example in http://sqlfiddle.com/#!2/5c3926/1, but I post it here as well ) Table :

 create table table1(table1_id int auto_increment not null primary key, 
  col1 char(1) not null,col2 int );    
insert into table1(col1)
values ('d'), ('a'), ('b'), ('y'), ('x')
, ('a'), ('g'), ('y'), ('d'), ('d'), ('d')
, ('t'),('b'), ('y'),('y');

SELECT which returns desired value (hopefully) in "rank_column":

select a.*, 
  (
  select count(1) from table1 c 
WHERE c.table1_id >      
    IFNULL
    (
      (
       select MAX(table1_id) as next_id 
       from table1 b where col1='Y' and b.table1_id <a.table1_id
      )
    ,0)

   and c.table1_id <= a.table1_id
  )
as rank_column
 from table1 a

Now update itself which just repeats previous select :

  UPDATE table1 curr_val 
  INNER JOIN 
  (
  select a.table1_id,  
  (
  select count(1) from table1 c 
WHERE c.table1_id >      
    IFNULL
    (
      (
       select MAX(table1_id) as next_id 
       from table1 b where col1='Y' and b.table1_id <a.table1_id
      )
    ,0)

   and c.table1_id <= a.table1_id
  )as rank_column

 from table1 a
)new_val ON (curr_val.table1_id = new_val.table1_id)
SET curr_val.col2 = new_val.rank_column

Best Answer

Related Solutions

Mysql – How to improve this nested MySQL query to avoid redundant WHERE statement

Mysql – How to do two actions in conditional UPDATE

Related Question