python - How do I update my stock database to reduce itself once a customer orders

Question

Time to ask from the almighty stack overflow questions again. So my problem is that I am developing in TKINTER and have implemented and integrated 2 tables in one database on the application. I would like my table products to update once an order is placed on basket table and it happens once the self.data_entry method is called. The thing is when my stock_update method runs, it updates the database but it wipes the stock value of the other products. I'm just wondering if my stock_update method has the wrong syntax for updating sql.

BEFORE Running

product_id	product_name	stock
1	product1	60
1	product2	60

question from:https://stackoverflow.com/questions/65910974/how-do-i-update-my-stock-database-to-reduce-itself-once-a-customer-orders

Answer 1

I revised the query. Hope this work:

stock_update_query = "UPDATE products SET stock = stock - (SELECT quantity FROM basket WHERE " 
                     "basket.product_id = " 
                     "products.product_id) " 
                     "WHERE EXISTS (SELECT quantity " 
                     "FROM basket "
                     "WHERE product_id = products.product_id)"