Is there one query that can update the "sequence number" for multiple groups?

Question

For a table like the one below, is there a way to update the table from a single query:

| id | type_id | created_at | sequence | |----|---------|------------|----------| | 1 | 1 | 2010-04-26 | NULL | | 2 | 1 | 2010-04-27 | NULL | | 3 | 2 | 2010-04-28 | NULL | | 4 | 3 | 2010-04-28 | NULL | 

To do this (note that created_at used for ordering, and the sequence "grouped" by type_id ):

 | id | type_id | created_at | sequence | |----|---------|------------|----------| | 1 | 1 | 2010-04-26 | 1 | | 2 | 1 | 2010-04-27 | 2 | | 3 | 2 | 2010-04-28 | 1 | | 4 | 3 | 2010-04-28 | 1 | 

I saw some code before using the @ variable, as shown below, which I thought might work:

 SET @seq = 0; UPDATE `log` SET `sequence` = @seq := @seq + 1 ORDER BY `created_at`; 

But this, obviously, does not reset the sequence to 1 for each id_type.

If there is no single request for this, what is the most efficient way?

The data in this table may be deleted, so I plan to start the stored procedure after the user has edited to re-sequence the table.

Answer 1

You can use another variable storing the previous id_type ( @type_id ). The request is ordered by type_id , so whenever a change to type_id , the sequence should be reset to 1. again.

 Set @seq = 0; Set @type_id = -1; Update `log` Set `sequence` = If(@type_id=(@type_id:=`type_id`), (@seq: =@seq +1), (@seq:=1)) Order By `type_id`, `created_at`; 

Answer 2

I do not know MySQL very well, but you can use an additional query, although it can be very slow.

 UPDATE 'log' set 'sequence' = ( select count(*) from 'log' as log2 where log2.type_id = log.type_id and log2.created_at < log.created_at) + 1 

You will get duplicate sequences, though if two type_names have the same create_at date.