Is there a single query that can update a "sequence number" across multiple groups?

Given a table like below, is there a single-query way to update the table from this:

| id | type_id | created_at | sequence |
|----|---------|------------|----------|
|  1 |       1 | 2010-04-26 | NULL     |
| 开发者_StackOverflow中文版 2 |       1 | 2010-04-27 | NULL     |
|  3 |       2 | 2010-04-28 | NULL     |
|  4 |       3 | 2010-04-28 | NULL     |

To this (note that created_at is used for ordering, and sequence is "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've seen some code before that used an @ variable like the following, that I thought might work:

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

But that obviously doesn't reset the sequence to 1 for each type_id.

If there's no single-query way to do this, what's the most efficient way?

Data in this table may be deleted, so I'm planning to run a stored procedure after the user is done editing to re-sequence the table.

You can use another variable storing the previous type_id (@type_id). The query is ordered by type_id, so whenever there is a change in type_id, sequence has to 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`;

I don't know MySQL very well, but you could use a sub query though it may 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'll get duplicate sequences, though, if two type_ids have the same created_at date.