mysql UPDATE row containing highest value with data from another table

I've seen example for the other way round (update target table with highest value from subject).

I want to update table_A's row which has the highest value with data found in table_B. Both have a common index.

Some tables might help.

Table A

+-------+----------+---------------+
| index | id_order | product_price |
+-------+----------+---------------+
|     1 |        1 | 10.00         |
|     2 |        1 | 12.00         |
|     3 |        2 | 5.00          |
|     4 |        3 | 4.00          |
+-------+----------+---------------+

Table B

+----------+------------------+
| id_order | price adjustment |
+----------+------------------+
|        1 | 4.00             |
|        2 | 1.00             |
|        3 | 2.00             |
+----------+------------------+

Table A - POST UPDATE

+-------+----------+---------------+
| index | id_order | product_price |
+-------+----------+---------------+
|     1 |        1 | 10.00         |
|     2 |        1 | 8.00          |
|     3 |        2 | 4.00          |
|     4 |        3 | 2.00          |
+-------+----------+---------------+

So row 2 (highest of order 1) has the adjustment applied but row 1 is left alone. Rows 3 and 4 are each adjusted by their respected values as they are singletons.

Answers


UPDATE a
SET value = (
    a.value - (
        SELECT value
        FROM b
        WHERE b.id = a.id
    )
)
WHERE a.id = 1
ORDER BY value DESC LIMIT 1 

Everything should be as you want now, this is setting the value in table a where id = 1 to the value it has minus the value in table where id also is 1.


Need Your Help

Enable Tiles devMode like Struts' devMode to reload tiles.xml with each request

struts2 struts tiles tiles2

Does Apache Tiles have a devMove like Struts that would reload the tiles.xml file with each request? If so, how can this be enabled?