CODEWITHSUNDEEP
phpmysql
kouton
kouton

Reputation: 1961

Reorder the whole MySQL table based on column values

I have a MySQL table with about 17mn records with an auto_increment field. The structure is something like this:

+--------+-------------------+-----------+
| ID     | Year1             |  Year2    |
+--------+-------------------+-----------+
| 1      | 2001              | 2         |
| 2      | 2001              | 1         |
| 3      | 1999              | 3         |
| 4      | 2004              | 2         |
+--------+-------------------+-----------+

I'd like to reorder the physical structure of the whole table by changing the auto_increment ID to match the Year1 and Year2 columns when ordered in ascending order. Something like this..

+--------+-------------------+-----------+
| ID     | Year1             |  Year2    |
+--------+-------------------+-----------+
| 1      | 1999              | 3         |
| 2      | 2001              | 1         |
| 3      | 2001              | 2         |
| 4      | 2004              | 2         |
+--------+-------------------+-----------+

Is this possible with a query in MySQL or should I use PHP and loops, et al.?

Update The use case is this: The actual data I have is stored in a specific pattern. I wouldn't want the end user to identify the pattern (2001, then 2001, then 1999, etc).. Hence I'd like to rearrange the data in a standard, sorted fashion..

There are no foreign keys for this table.

Upvotes: 2

Views: 1120

Answers (1)

Himanshu
Himanshu

Reputation: 32602

First of all take a backup of your database

Instead of re arranging your actual table create a new table and insert values from the old one. like this:

Create table with auto increment, primary key

CREATE TABLE Table2
    (`ID` int auto_increment primary key, `Year1` int, `Year2` int)
;

Insert with SELECT statement

INSERT INTO Table2
       (`Year1`, `Year2`)
SELECT Year1, Year2
FROM Table1
ORDER BY Year1, Year2;

See this SQLFiddle

Upvotes: 2

Related Questions