DanielJay
asked on
SQL Local Variable Subtraction
Hi - I am having a problem with this SQL at subtracting QuantityRemaining, when I run this code it reduces QuantityRemaining to NULL, however if I replace the @QSold in
"UPDATE Listings SET Status = 'Sold', QuantityRemaining = QuantityRemaining - @QSold WHERE SKU = 'TEST005'"
to a number it works?
Both QuantityRemaining and ItemCount have integer as their datatype in the tables.
Any ideas?
DECLARE @QRemain int
DECLARE @QSold int
SET @QRemain = (SELECT QuantityRemaining FROM Listings WHERE SKU = 'TEST005' AND ID = '1b1d')
SET @QSold = (SELECT ItemCount FROM Transactions WHERE OrderNumber = 'TEST005' AND ID = '1b1d')
IF EXISTS (SELECT SKU FROM Listings WHERE SKU = 'TEST005' AND ID = '1b1d')
BEGIN
UPDATE Listings SET Status = 'Sold', QuantityRemaining = QuantityRemaining - @QSold WHERE SKU = 'TEST005'
UPDATE Transactions SET Reconciled = 1 WHERE SKU = 'TEST005'
END
"UPDATE Listings SET Status = 'Sold', QuantityRemaining = QuantityRemaining - @QSold WHERE SKU = 'TEST005'"
to a number it works?
Both QuantityRemaining and ItemCount have integer as their datatype in the tables.
Any ideas?
DECLARE @QRemain int
DECLARE @QSold int
SET @QRemain = (SELECT QuantityRemaining FROM Listings WHERE SKU = 'TEST005' AND ID = '1b1d')
SET @QSold = (SELECT ItemCount FROM Transactions WHERE OrderNumber = 'TEST005' AND ID = '1b1d')
IF EXISTS (SELECT SKU FROM Listings WHERE SKU = 'TEST005' AND ID = '1b1d')
BEGIN
UPDATE Listings SET Status = 'Sold', QuantityRemaining = QuantityRemaining - @QSold WHERE SKU = 'TEST005'
UPDATE Transactions SET Reconciled = 1 WHERE SKU = 'TEST005'
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER