Reputation: 599
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
Upvotes: 1
Views: 294
Reputation: 4042
If you know the max-value of order
, you can simply do (assuming max(order) = 2
)
UPDATE `myTable` SET `PORDER` = 2 - `PORDER`
Example:
+--------+------------+
| PORDER | 2 - PORDER |
+--------+------------+
| 0 | 2-0 = 2 |
| 1 | 2-1 = 1 |
| 2 | 2-2 = 0 |
+--------+------------+
Upvotes: 1
Reputation: 6192
try this
INSERT INTO myTable(`porder`, x, y) SELECT (SELECT MAX(`porder`) FROM myTable) - `porder`, x, y FROM myTable
Upvotes: 0