EXPLAIN EXTENDED

Yesterday I wrote an article about selecting random rows efficiently.

But today on Stack Overflow:

Hi

I wish to attach a column to my table which will be a random number from a sequential list = to the number of rows.

So, if my table had 999 rows, then the numbers 1 to 999 would be assigned randomly and uniquely.

Now, I figured that I could add a dummy TempRandomColumn=Rand(), sort by that and add the numbers sequentially using PHP. But that means 999 MySQL statements.

Is there a way to do this using a single MySQL statement?

Thanks for any pointers.

Well, it's just that simple:

Creating tables here:

CREATE TABLE t_zen (zen_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, zen_value VARCHAR(20) DEFAULT '無', zen_order INT);
INSERT
INTO   t_zen (zen_order)
VALUES
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL)

and performing an update:

SET @r := 0;
UPDATE t_zen
SET    zen_order = (@r := @r + 1)
ORDER BY
       RAND(20030302)

ORDER BY RAND() certainly has some beauty in it.