问题描述:

UPDATE Products

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

ShoppingCart:

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:

Products:

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.

相关阅读:
Top