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

Question

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

What's the error message? Have you thought about using a temporary table? CREATE TABLE TMP .... INSERT INTO TMP SELECT ... myTable. DROP TABLE myTable. RENAME TABLE TMP TO myTable. — Basti
– Basti, Commented Apr 10, 2012 at 0:35

Ankit Sharma · Accepted Answer · 2012-04-10 08:34:21Z

1

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   |
   +--------+------------+

edited Apr 10, 2012 at 8:34

Ankit Sharma

4,0792 gold badges31 silver badges50 bronze badges

answered Apr 10, 2012 at 0:37

Basti

4,0511 gold badge21 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MatthewLee Over a year ago

unfortunately I don't know the max, it changes from case to case, but thank you.

MatthewLee Over a year ago

So I used a separate query to get the max value and then used @nauphal's solution above. So thank you for the idea!

Nauphal · Accepted Answer · 2012-04-10 06:03:15Z

0

try this

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

answered Apr 10, 2012 at 6:03

Nauphal

6,1824 gold badges30 silver badges43 bronze badges

2 Comments

MatthewLee Over a year ago

unfortunately the SELECT MAX causes it to only select 1 row from each set of points. Oddly it also returned that row with the porder of 0, I would have expected it to be 2 (porder max of 2 - porder of 0 = 2) but I will play some more with this as it seems to have promise

MatthewLee Over a year ago

so I gave your answer and @Basti's both an up vote because combining the 2 got me on the right track. I had to had a second query to pull the max porder and assign it to a variable so not quite as clean as I would have liked but it works. If someone has a more elegant solution I would appreciate seeing it.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related