database - How to subtract values from two different sqlite3 tables in python

Question

Assuming i have a main table containing a list of items and quantities. And a second table having also a list of items and quantities. Eg.

Main Table(Stock)(Table1)

----------
Items  | QTY
----------
sugar  | 14
mango  | 10
apple  | 50
berry  | 1

Second Table(populated by user input)(Table 2)

----------
Items  |QTY
----------
sugar  |1
mango  |5
apple  |8
berry  |1

How do i get the item and quantity from the table 2, compare the item name with that of Table 1,such that if same it moves on to subtract Table 2's value from that of Table 1 In summary, how do i subtract values from two different tables in sqlite3 python Any ideas would help so much. A simple code example would also help much.Thank you

Answer 1

You can use replace into with join:

replace into Stock(Items, qty)    
select s.Items,
    s.qty - t.qty
from Stock s
join Second_table t on s.Items = t.Items;

The above works if there is a unique key defined on the Items column.

You can try using correlated subquery:

update stock
set qty = qty - (
        select sum(qty)
        from second_table t
        where stock.items = t.items
        )