Posted by diablo on 11/16/05 15:34

> > You sure should handle the situation that the thing you are
> > substracting runs out of stocks, but the query should be:
> >
> > UPDATE product
> > SET quantity = quantity - c.quantity
> > FROM product p
> > INNER JOIN Cart c
> > ON p.productid=c.productid
> >
> >
> > HTH, Jens Suessmeyer.
>
> Jens,
> I think diablo will want to add "WHERE c.buyerid=x" to your query. Even
> then the quantity value will not be correctly updated unless
> (buyerid,productid) is unique, which it may be, although that wasn't
> explicitly stated.


ooops sorry buyerid is NOT unique

Cart table
CartID - Unique
BuyerID - Integer
ProductID - Integer
Quantity - Integer

Product table
ProductID - Unique
PStock - Integer


The cart table will have mutiple entries for the same buyerid. so select
'where buyerid=7' returns 3 rows from Cart table.

Sorry for the confusion

Kal

[Back to original message]