Link to home
Start Free TrialLog in
Avatar of DanielJay
DanielJayFlag for United Kingdom of Great Britain and Northern Ireland

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
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DanielJay

ASKER

This was returning null so now fixed.