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: 193
mysql
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