SET Products.Quantity = Products.Quantity + ShoppingCart.Quantity
FROM Products INNER JOIN ShoppingCart
ON ShoppingCart.ProductID = Products.ProductID
WHERE ShoppingCart.DateAdded < GETDATE()- 7
DELETE FROM ShoppingCart
WHERE DateAdded < GETDATE()- 7
and this query need to do this job:
Products that are added in ShoppingCart that are older then 7 days are to be deleted and their quantity is to be returned back into the available quantity of that product for sale. But the issue here is that only the 1st product's quantity is added back in Products.Quantity field. The rest of them are not counted and added back into the Products table..
I assumed the following initial data: Products:
PRODUCTID QUANTITY 1 10 2 210 3 0
CARTID PRODUCTID QUANTITY DATEADDED 21 1 3 2012-11-20 24 1 1 2012-11-19 22 2 2 2012-11-25 23 3 3 2012-11-20
You would get the following output:
PRODUCTID QUANTITY 1 14 2 210 3 3
What's happening is that for every row in Products, the Quantity is being updated "x" times where "x" is the number of matching rows in ShoppingCart. However, on each iteration the value held by "Products.Quantity" is the initial value, so the final result is simply the addition of the initial value in Products.Quantity plus the Quantity value from the last matching record in ShoppingCart.
For example, in the dataset above the following equivalent actions would happen in the database for product 1:
UPDATE Products SET Quantity = 10 + 1 WHERE ProductId = 1; UPDATE Products SET Quantity = 10 + 3 WHERE ProductId = 1;
So the final value is 13, and not 14 as you'd expect. Basically, the last row evaluated in the INNER JOIN wins and the rest end up being effectively ignored.
To get around this you need aggregate the data before you do the set command. I believe the following will do the trick. I'm summing all of the expired quantities for each product in an inline view and then using that value for the updates:
UPDATE Products SET Products.Quantity = Products.Quantity + Expired.Quantity FROM Products INNER JOIN (SELECT S.ProductId, SUM(S.Quantity) AS Quantity FROM ShoppingCart S WHERE S.DateAdded < GETDATE()- 7 GROUP BY S.ProductId) AS Expired ON Expired.ProductID = Products.ProductID
I used a SQL Fiddle to test this.