1

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.

  • 1
    Don't use AUTO_INCREMENT if you intend to manipulate the ids. – Rick James Apr 8 '15 at 12:36
  • @RickJames I have upvoted you, but I interpret it as that he/she doesn't intend to manipulate the ids, it was an import issue and a one-time fix is needed. – jynus Apr 8 '15 at 14:00
  • @jynus - valid point. However, we don't, know what the id's are for. Perhaps PRIMARY KEY(date, name) would be appropriate? (and not have id) – Rick James Apr 8 '15 at 18:46
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.