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

Posted by Drarok on Stack Overflow See other posts from Stack Overflow or by Drarok
Published on 2010-04-30T10:00:38Z Indexed on 2010/04/30 10:17 UTC
Read the original article Hit count: 192

Filed under:

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     |
|  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.

© Stack Overflow or respective owner

Related posts about mysql