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

> > 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
> >
>
> 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.

Yes - Sorry that is correct i will be adding that and buyerid is unique :-)



> Diablo,
> Please include DDL with future posts (CREATE TABLE, with keys and
> constraints) then we don't have to makes guesses about your tables.

OK will do.

here is the query

UPDATE product_t
SET pstock = pstock - c.quantity
FROM product_t p
INNER JOIN cart_t c
ON p.productid=c.productid
where c.buyerid=7


However the issue still remains that Jens code gives a syntax error
missing operator: 'quantity - c.quantity FROM product p ...'

thanks for help

Kal

[Back to original message]