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:
Read the rest of this entry »