在sql表中反转id

Question

i have a table with primary id
1 simer
2 pawan
3 deep
4 sheep
5 man
i want reverse this order
5 simer
4 pawan
3 deep
2 sheep
1 man

how can i do this with programming or sql? i dont want to use order by in sql

Answer 1

Either:

1) Select all the rows ordered by id in descending order, then insert them into a new table in ascending order, and that table's AUTO_INCREMENT will assign the identifiers in reverse order. Now you can copy those rows back to the original table with their new identifiers.

2) Select all the rows ordered by id in descending order into a program, delete them, and reinsert them with the new IDs:

$sql = "SELECT id, name FROM table ORDER BY id DESC";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
}

//empty the table, to avoid primary key conflicts
mysql_query("TRUNCATE TABLE table");    

$i = 1;
foreach ($rows as $row) {
  mysql_query("INSERT INTO table (id, name) VALUES ($i, '" . mysql_real_escape_string($row['name']) . ")");
  $i++;
}