EXPLAIN EXTENDED

From Stack Overflow:

I have the following table:

Id	Value
1	3
1	12
1	67
2	7
2	99
5	30
5	33
5	4
5	87
5	12
5	1

I'd like to update it:

Id	UniqueIdBySubSet	Value
1	1	3
1	2	12
1	3	67
2	1	7
2	2	99
5	1	30
5	2	33
5	3	4
5	4	87
5	5	12
5	6	1

Does anyone know how can I do that in MySQL?

These kinds of things are easily done with ROW_NUMBER() in systems that support it.

This function can be emulated in MySQL too. Some time ago I wrote an article on how to do that:

• Analytic functions: SUM, AVG, ROW_NUMBER

This task, however, is a little more complex. Since we update a single field, we need to change two variables at once in one SET clause.

We cannot use JOIN's here too since we don't yet have a PRIMARY KEY to join on.

However, session variable support in MySQL is flexible enough to do that.

Let's create the table and see how do we do it:

CREATE TABLE t_sequence (
        Id INT NOT NULL,
        UniqueIdBySubSet INT,
        Value INT NOT NULL
) ENGINE=InnoDB;

INSERT
INTO    t_sequence (Id, Value)
VALUES
(1, 3),
(1, 12),
(1, 67),
(2, 7),
(2, 99),
(5, 30),
(5, 33),
(5, 4),
(5, 87),
(5, 12),
(5, 1);

UniqueIdBySubSet is not filled yet:

SELECT  *
FROM    t_sequence
ORDER BY
        id, value

To emulate ROW_NUMBER, we need two variables here: the first one, @id will indicate the id change; the second, @r, will be the counter.

We will need to increment @r whenever @id remains the same, and reset it to 1 when @id changes.

Here's one way to do it:

SET @r := 1;
SET @id := 0;
UPDATE  t_sequence s
SET     s.uniqueidbysubset = IF(@id = id, @r := @r + 1, @r := (@id := id) - id + 1)
ORDER BY
        id, value

If the previous value of id (stored in @id) is equal to the current one, we increment @r.

If it's not, we both assign new value to @id and use the return value of the assignment to reset @r.

Here's what we get after the UPDATE completes:

SELECT  *
FROM    t_sequence
ORDER BY
        id, value

We now have a sequential set of numbers inside each group.