CODEWITHSUNDEEP
mysqlsql
Kawyllainy Vi
Kawyllainy Vi

Reputation: 39

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'

Upvotes: 0

Views: 66

Answers (2)

sdabet
sdabet

Reputation: 18670

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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.

Upvotes: 2

Related Questions