I have 2 tables that i join with LEFT JOIN function tableA & tableB
- data1,col1 and data2,col2 are the references column I use to match both tables entries
- data3 is a number I use to sum with value from TableB.col3
- data4 is the value I want to update based on the sum of tableA.data3 + tableB.col3
TableA TableB
data1,data2,data3,data4 col1,col2,col3
10001,Feb-2019,100, 10001,Feb-2019,43,
10001,Mar-2019,201, 10001,Mar-2019,22,
10002,Feb-2019,123, 10003,April-2019,23,
10003,April-2019,53,
...
COMMAND:
SELECT data1, data2, data3, col3 ,data3+col3 from tableA tA LEFT JOIN tableB tB ON tA.data1=tB.col1 AND tA.data2=tB.col2;
10001|Feb-2019|100|43|143
10001|Mar-2019|201|22|223
10002|Feb-2019|123||
10003|April-2019|53|23|76
...
I can get the desire result with the SELECT statement
SELECT data1, data2, data3, IFNULL(col3,0) ,data3+IFNULL(col3,0) from tableA tA LEFT JOIN tableB tB ON tA.data1=tB.col1 AND tA.data2=tB.col2;
10001|Feb-2019|100|43|143
10001|Mar-2019|201|22|223
10002|Feb-2019|123|0|123
10003|April-2019|53|23|76
...
I want the result of the SUM to be set in tableA.data4 to get the following restult
SELECT * from tableA;
10001|Feb-2019|100|143
10001|Mar-2019|201|223
10002|Feb-2019|123|123
10003|April-2019|53|76
Many thanks
You can do it like this:
UPDATE TableA
SET data4 = data3 + COALESCE((
SELECT IFNULL(tB.col3, 0)
FROM tableB tB
WHERE TableA.data1=tB.col1 AND TableA.data2=tB.col2
), 0);
See the demo.
Results:
| data1 | data2 | data3 | data4 |
| ----- | ---------- | ----- | ----- |
| 10001 | Feb-2019 | 100 | 143 |
| 10001 | Mar-2019 | 201 | 223 |
| 10002 | Feb-2019 | 123 | 123 |
| 10003 | April-2019 | 53 | 76 |