如何从python中的两个不同的sqlite3表中减去值

问题描述：

假设我有一个包含项目和数量列表的主表.第二张表也列出了物品和数量.例如

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.

主表(库存)(表1)

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

第二张表(由用户输入填充)(表2)

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

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

我如何从表2中获得项目和数量，将项目名称与表1中的名称进行比较，以便如果名称相同，则继续从表1中减去表2的值总而言之，我如何从sqlite3 python中的两个不同表中减去值任何想法都会有很大帮助.一个简单的代码示例也有很大帮助.谢谢

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

答

您可以将 replace into 替换为 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
        )

如何从python中的两个不同的sqlite3表中减去值

相关推荐