Triangular Exchange in SQL? (Via PHP)

Question

I have a SQL table which looks like:

---------------------
| price  |price_two |
---------------------
| 35.90  |     0    |
| 21.90  |     5    |
| 7.90   |     0    |
| 50.21  |   30.29  |
---------------------

And now I want to create a query in SQL which switches all values from price to price_two, where price_two is lower than price but NOT 0.

So, at the end I want this:

---------------------
| price  |price_two |
---------------------
| 35.90  |     0    |
|   5    |   21.90  |
| 7.90   |     0    |
| 30.29  |   50.21  |
---------------------

Can I do that and if yes, how?

Greetings and Thank You!

you should obtain 30.9 | 50.21 for last row ...not 50.21 | 30.29 — ScaisEdge, May 01 '17 at 19:21
I removed my answer. This is more complex than it looks and has already been answered: http://stackoverflow.com/questions/37649/swapping-column-values-in-mysql — Jeremy Harris, May 01 '17 at 19:26
Possible duplicate of [Swapping column values in MySQL](http://stackoverflow.com/questions/37649/swapping-column-values-in-mysql) — Jeremy Harris, May 01 '17 at 19:26
@JeremyHarris I think I like one of the comments the best: `x=x+y,y=x-y,x=x-y;` from izak; but that's a bit of a maintenance headache for someone lacking the logical critical thinking skills. — xQbert, May 01 '17 at 20:25

xQbert · Accepted Answer · 2017-05-01T20:22:37.023

Something like...

SQL Fiddle

UPDATE Price P
INNER JOIN Price P2
  on P.Price = P2.Price
 AND P2.Price_two = P.Price_two
 AND P.Price_two < P.Price
 AND P.Price_two != 0

 SET P.Price = P2.Price_two,
     P.Price_two = P2.Price;

We simply use a self join and use the 2nd table's data to update the first since we need to materialize the values and perform both updates at once or we lose the handle to original values!

Triangular Exchange in SQL? (Via PHP)

1 Answers1