CODEWITHSUNDEEP
phpmysql
MatthewLee
MatthewLee

Reputation: 599

insert incremental values in an insert ... select statement mysql

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

Answers (2)

Basti
Basti

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

Nauphal
Nauphal

Reputation: 6192

try this

    INSERT INTO myTable(`porder`, x, y) SELECT (SELECT MAX(`porder`) FROM myTable) - `porder`, x, y FROM myTable

Upvotes: 0

Related Questions