Kawyllainy Vi Kawyllainy Vi - 4 months ago 10
MySQL Question

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

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'

Answer

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.