MySQL sum two tables fields and UPDATE

Question

Im trying how to sum id by id two tables and update the one :

STOCKA

---------
|P | U |
|-------|
| 1 | 1 |
| 2 | 0 |
| 3 | 3 |
---------

STOCKB

--------- 
|P | U |
|-------|
| 1 | -3 |
| 2 | -2 |
| 3 | -1 |
---------

Results I want :

STOCKA

--------
|P | U |
|--------|
| 1 | -2 |
| 2 | -2 |
| 3 | 2 |
---------

My query :

UPDATE STOCKA,STOCKB
SET STOCKA.U =  STOCKA.U - ABS(STOCKB.U)
WHERE
STOCKA.P = STOCKB.P

in my query the results values are wrong, i use the answer of hjpotter92. Thanks :) — sarasa, Oct 19 '13 at 07:47

score 0 · Accepted Answer · answered Oct 17 '13 at 22:28

0

Use JOIN instead of trying to pass two tables in an UPDATE statement:

UPDATE STOCKA a
JOIN STOCKB b
    ON a.P = b.P
SET a.U =  a.U - ABS(b.U)

answered Oct 17 '13 at 22:28

hjpotter92

78,589
36
144
183

Thanks! for modify my post and the correct answer. This is great ! :D – sarasa Oct 17 '13 at 23:29

Gordon Linoff · Answer 2 · 2013-10-20T16:16:58.023

0

The query in your question should work, if there is only one row in the "b" table for each P. If there can be more than one row, then aggregate first and then join:

UPDATE STOCKA a join
       (select P, sum(abs(u)) as bu
        from STOCKB
        group by P
       ) b
       on a.P = b.P
    SET a.U =  a.U - b.bu;

edited Oct 20 '13 at 16:16

answered Oct 17 '13 at 22:37

Gordon Linoff

1,242,037
58
646
786

MySQL sum two tables fields and UPDATE

2 Answers2