Jake Wilson Jake Wilson - 2 months ago 6
MySQL Question

MySQL query to reorder field values?

I have table like this:

===============
| rank | name |
===============
| 3 | john |
| 6 | bob |
| 10 | alex |
| 11 | brad |
| 12 | matt |
| 34 | luke |
| 145 | ben |
===============


(this table is an example. In reality my table consists of ~5000 rows of data).

Is there a query to reorder the rank values starting from 1 and going up so it ends up like this:

===============
| rank | name |
===============
| 1 | john |
| 2 | bob |
| 3 | alex |
| 4 | brad |
| 5 | matt |
| 6 | luke |
| 7 | ben |
===============


It would be preferable to do this in 1 or 2 queries, not 1 query for each row since my table has 5000+ rows.

EDIT: Sorry I wasn't clear. I am trying to UPDATE the values in the database.

Answer

Here is the solution I came up with for this problem:

1.Create a temporary table without any keys

CREATE TEMPORARY TABLE tempTable (
  id INT(11) NOT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT;

2.Populate the temporary table with data from the original table, ordered by rank

INSERT INTO tempTable SELECT id FROM myTable ORDER BY rank;

3.Add auto-incrementing rank column, giving all rows a unique rank, counting up from 1

ALTER TABLE tempTable
    ADD COLUMN `rank` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY (`rank`);

4.Update the original table with a join to the temp table, overriding the original ranks

UPDATE myTable 
INNER JOIN tempTable
    ON myTable.id = tempTable.id
SET myTable.rank = tempTable.rank;

5.Drop the temp table

DROP TABLE tempTable;