Mysql – Reverse auto_increment id existing column in phpMyAdmin

auto-incrementcsvMySQLorder-byphpmyadmin

I have imported csv file through phpMyAdmin into one table. I have set auto-increment field, as shown below, but of course it ordered it ascending:

+----+------------+------+
| id | date       | name |
+----+------------+------+
| 1  | 2015-02-28 | Mike |
| 2  | 2015-02-26 | Sara |
| 3  | 2015-02-22 | Luke |
+----+------------+------+

So now, when I add new user, it gives him id=4. But what I want is to reverse the order of the above table, to reverse id, so when I add new user, because of the date it will be ordered according to the date.

I cannot change the csv file, I need to do something in PhpMyAdmin to change the ids (auto-increment) – to reverse them.
It should look like this:

+----+------------+------+
| id | date       | name |
+----+------------+------+
| 1  | 2015-02-22 | Luke |
| 2  | 2015-02-26 | Sara |
| 3  | 2015-02-28 | Mike |
+----+------------+------+

How to achieve this? As I want when adding new user to give him the id=4, but the date will be todays date and in the whole table it will be sorted by date normally.

Best Answer

You can create a new table like the old one and insert the values in a different order, for example:

CREATE TABLE new_table like old_table;
INSERT INTO new_table (date, name) 
SELECT date, name FROM old_table 
ORDER BY date ASC, id DESC

Change the ORDER BY to whatever suits to you- I have ordered by date so it doesn't trust that the original data was right, you can order just by id DESC.

You can later delete the old table and rename the new table as the old table:

DROP TABLE old_table;
RENAME TABLE new_table TO old_table;

It can be done just using a table, but that is easier and lets you check that the contents are ok before changing the original table.

If you did something like this the ids will collide and the query would fail -it would require an additional column or the drop of the PK and two table rewrites instead of one:

SELECT max(id) INTO @maxid FROM my_table;
UPDATE my_table SET id = @maxid - id + 1;
Related Question