Mysql – How to do two actions in conditional UPDATE

MySQLupdate

In a query as

SET @x = 1

UPDATE table1 SET
col2 = IF(col1 = 'y', @x:=0, @x:=@x+1)

Every time reaching the value of y in col1, I want to SET the @x:=@x+1 and after that reset the x to 0.

In other words, I want to count the rows before the occurrence of rows ending to col1 = 'y'.

EXAMPLE

I want to UPDATE col2 as follows. In the above-mentioned query, rows for col2 is simply 0 for all col1 = 'y'.

id       col1       col2
1       d          1
2       s          2
3       w          3
4       y          4
5       s          1
6       c          2
7       y          3
8       s          1
9       y          2
10       s          1

Best Answer

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
Related Question