SQL Query Update subtract variable

Question

I am working on developing an inventory application. I have a table ShopInventory_Parts

When a user selects an item from the table, a window appears.

From here, the data from the item selected in the table is displayed in their respective fields. The last field is where the user is able to enter in the quantity that they will be "Checking Out" from the inventory.

When the user enters in the quantity checked out, I have a SQL script that will write to another table called ShopInventory_Parts_Checkout - and this is working correctly

My query for this is:

        query = "INSERT INTO ShopInventory_Parts_Checkout (Name,Manufacturer,PartNum,assetID,quantityCheckedOut,t_stamp) VALUES (?,?,?,?,?,?)"
        args = [Name,Manufacturer,PartNum,assetID,quantityCheckedOut,t_stamp]
        result = system.db.runPrepUpdate(query,args)

My question is, how can I get the 'quantity' in my ShopInventory_Parts table to subtract from the quantity based on the user entry?

Right now, I have the following SQL query and it is returning "NULL"

        query = "UPDATE ShopInventory_Parts SET quantity = (quantity - updatedQuantity) WHERE assetID=(?)"
        args = [assetID]
        result = system.db.runPrepUpdate(query,args)

I apologize if this seems very basic, but I just cannot get it working. Any help would be greatly appreciated.

Thank you

Answer 1

The Python variable needs to be in the args list, not the SQL. And pass the quantity being checked out, not the updated quantity (otherwise the two calculations cancel each other out).

query = "UPDATE ShopInventory_Parts SET quantity = quantity - ? WHERE assetID = ?"
args = [quantityCheckedOut, assetID]
result = system.db.runPrepUpdate(query,args)