Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
share|improve this question
    
This is not clear. Please clarify with a real example (table with values and expected result after the update.) –  ypercube Jan 8 at 19:24
    
@ypercube I added an example. –  All Jan 8 at 19:30
    
There is no inherent order in the rows within a table. Your example is not ordered alphabetically. What do you want the result to be sorted by? –  Sebastian Meine Jan 8 at 19:31
    
@SebastianMeine sorry I forgot to include the PRIMARY_KEY, which is AUTO_INCREMENT. –  All Jan 8 at 19:33
    
removed mine answer with "IF(col1 = 'y', (@x:=@x+1), @x=0)" because it indeed didnt make any sense after the topicstarters update "In other words, I want to count the rows before the occurrence of rows ending to col1 = 'y'." off the topic starter... –  Raymond Nijland Jan 8 at 20:01
add comment

1 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
share|improve this answer
add comment

Your Answer

 
discard

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.