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 &gt;      
    IFNULL
    (
      (
       select MAX(table1_id) as next_id 
       from table1 b where col1='Y' and b.table1_id &lt;a.table1_id
      )
    ,0)

   and c.table1_id &lt;= 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 &gt;      
    IFNULL
    (
      (
       select MAX(table1_id) as next_id 
       from table1 b where col1='Y' and b.table1_id &lt;a.table1_id
      )
    ,0)

   and c.table1_id &lt;= 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