How can I make the sum of two columns of two different tables?

Question

How can I make the sum of two columns of two different tables ?

In my case , I want sum the two columns of different table , and in the first table to return the value added .

Example :

Tbl_one

colum ID | colum Point
1        | 1000
2        | 2000

tbl_two

colum ID | colum Point
1        | 10000
2        | 5000

RESULT

Tbl_one

colum ID | colum Point
1        | 11000 
2        | 7000

I tried this query , but it did not work very well

UPDATE tbl_1 SET tlb_1.columX= tbl_1.columX + tbl_2.columY

RESULT ERROR: #1054 - Unknown column 'tbl_2.columY' in 'field list'

Duplicate perhaps -- you need to use `join` in your `update` statement: http://stackoverflow.com/a/15209489/1073631 — sgeddes, Aug 10 '16 at 13:59

score 2 · Accepted Answer · answered Aug 10 '16 at 13:58

Normally, one would use a select query for this:

select id, sum(point)
from ((select id, point from tbl_one) union all
      (select id, point from tbl_two)
     ) t
group by id;

If you actually want to update the table, one method uses join:

UPDATE tbl_1 t1 JOIN
       tbl_2 t2
       ON t1.id = t2.id
    SET t1.Point = t1.Point + t2.Point;

However, updating the table seems extreme, if all you want to do is get the aggregated results.

score 0 · Answer 2 · answered Aug 10 '16 at 13:58

0

I think you should make a join with tbl_two in your update request, which would like this:

UPDATE tbl_1 INNER JOIN tbl_2 ON tbl_2.ID = tbl_1.ID SET tbl_1.columX= tbl_1.columX + tbl_2.columY

answered Aug 10 '16 at 13:58

sdabet

2 Answers2