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

What is `updatedQuantity`. It doesn't appear in either table. — Barmar, Apr 20 '22 at 15:49
@Barmar The programming language is Python. I have a few defined variables in the script. quantityAvailable = self.getSibling("quantityAvailable").props.text (which is quantity from the ShopInventory_Parts table) then i have quantityCheckedOut = self.getSibling("quantityCheckedOut").props.value (which is the quantity the user enters). updatedQuantity = int(quantityAvailable) - int(quantityCheckedOut) — , Apr 20 '22 at 15:56
Did you check whether the SQL command is built incorrectly by your source code or whether it's correct, but does not lead to the desired result? — Jonas Metzler, Apr 20 '22 at 15:56

Barmar · Accepted Answer · 2022-04-20T16:07:08.723

2

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)

edited Apr 20 '22 at 16:07

answered Apr 20 '22 at 15:58

Barmar

741,623
53
500
612

When I did this, I ran the application and entered a 1 in for the quantity checking out. Instead of subtracting 1 and showing 99, the quantity in table ShopInventory_Parts showed a value of 1 rather than 99. – Apr 20 '22 at 16:03
Don't do the subtraction in Python. – Barmar Apr 20 '22 at 16:06
Got it, thank you very much! – Apr 20 '22 at 16:08

SQL Query Update subtract variable

1 Answers1