|
|
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
--
Navigation:
[Reply to this message]
|