CODEWITHSUNDEEP
sqlsqlitesql-update
a a
a a

Reputation: 11

Android SQLite update current table based on (sum of (current table + another table))

I've got two tables in Android SQLite dbHelper :

Table#1 :

itemName | PCS
---------+------
item1    | 5
item2    | 7
item3    | 3

and Table#2 :

itemName | PCS
---------+------
item2    | 2
item3    | 17

I need to update PCS column in Table#1 , add all PCS in table#2 into table#1(sum)\

I need this output :

updated Table#1 :

    itemName | PCS
    ---------+------
    item1    |  5
    item2    |  9
    item3    | 20

Upvotes: 1

Views: 85

Answers (2)

forpas
forpas

Reputation: 164099

You can use a correlated subquery that returns the value of pcs from Table2 and add it to the value of pcs of Table1:

update Table1
set pcs = pcs + coalesce((select pcs from Table2 where Table2.itemname = Table1.itemname), 0)

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65313

One option would be merging the rows of those tables through use of INSERT OR REPLACE INTO statement :

INSERT OR REPLACE INTO Table1(itemName,PCS)
SELECT t1.itemName, COALESCE(t1.PCS,0)+COALESCE(t2.PCS,0)
  FROM Table1 t1 
  LEFT JOIN Table2 t2 ON t2.itemName = t1.itemName;

provided that the itemName columns are defined as primary keys.

Demo

Upvotes: 0

Related Questions