How To Subtract Values From Two Different Sqlite3 Tables In Python

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) ---------- Item

Solution 1:

You can use replace into with join:

replace intoStock(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 - (
        selectsum(qty)
        from second_table t
        where stock.items = t.items
        )