Posted by David Portas on 11/16/05 15:20

The following works for me. Notice the key on Cart. Jens' UPDATE only
makes sense if the productid is unique for the given buyerid -
otherwise the total deducted from Product won't match the total of the
corresponding rows in Cart.

CREATE TABLE product (productid INTEGER NOT NULL, quantity INTEGER NOT
NULL /* PRIMARY KEY not specified */) ;
CREATE TABLE cart (buyerid INTEGER NOT NULL, productid INTEGER NOT
NULL, quantity INTEGER NOT NULL, /* ?? */ PRIMARY KEY
(buyerid,productid)) ;

INSERT INTO product (productid, quantity)
VALUES (1,100) ;

INSERT INTO cart (buyerid, productid, quantity)
VALUES (1,1,90) ;

UPDATE P
SET quantity = P.quantity - C.quantity
FROM product P
INNER JOIN Cart C
ON p.productid = c.productid
WHERE C.buyerid = 1 ;

SELECT * FROM product ;

Result:

productid quantity
1 10

(1 row(s) affected)

--
David Portas
SQL Server MVP
--

[Back to original message]