I am trying to select a number of rows in a table, reverse the values in one column and reinsert them into the table. Here is an example of what I am doing, say I have the following data:
+-------+--------+-------+
| ORDER | X | Y |
+-------+--------+-------+
| 0 | 12 | 5 |
| 1 | 16 | 3 |
| 2 | 19 | 2 |
+-------+--------+-------+
I want to select it and reinsert it into the same table with the ORDER reversed as so:
+--------+--------+-------+
| PORDER | X | Y |
+--------+--------+-------+
| 2 | 12 | 5 |
| 1 | 16 | 3 |
| 0 | 19 | 2 |
+--------+--------+-------+
I am able to duplicate the rows and reinsert them, no problem using an insert ... select like this:
INSERT INTO myTable (porder, x, y) SELECT porder, x, y FROM myTable
but I have had no success reversing the order. I have tried
INSERT INTO myTable (porder, x, y) SELECT (SELECT porder FROM myTable ORDER BY porder DESC), x, y FROM myTable but that throws an error
It would be fine to simply ignore the porder column and insert new values from 0 to the highest number in the sequence (2 in my above example) but I don't know how to add sequential numbers in a multiple-row insert statement in mysql.
I know how to do this with php but I was thinking there must be a more elegant solution in just SQL
