sqlitesumifnull

sqlite update left join two tables


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


Solution

  • 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    |