sql query update

[diablo]

Hi

i need help with formulating a query that will update the a field on one
table depending on the values from another for example

i have a cart table: cartid, buyerid, productid, quantity

i have a product table: productid, quantity

i want to do a:

select * from cart where buyerid=x

and then depending on the returned rows i want to update the product table:

update product set quantity = quantity - cart.quantity where
productid=cart.productid

if you see what i mean?

Any help appreciated

Kal

[David Portas]

UPDATE product
SET quantity = quantity -
(SELECT SUM(quantity)
FROM cart
WHERE cart.buyerid = x
AND cart.productid = product.productid)
WHERE EXISTS
(SELECT *
FROM cart
WHERE cart.buyerid = x
AND cart.productid = product.productid) ;

--
David Portas
SQL Server MVP
--

[Jens]

Hi Kal,
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.

[diablo]

>

UPDATE product
SET quantity = quantity - c.quantity
FROM product p
INNER JOIN Cart c
ON p.productid=c.productid

Hi

This looked like what i wanted but it gives an error
"missing operator" FROM...

Thank

Kal

[David Portas]

Jens wrote:

Hi Kal,
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.

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

--
David Portas
SQL Server MVP
--

[diablo]

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

[David Portas]

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

[diablo]

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

[David Portas]

See the UPDATE statements I posted. There's no requirement that buyerid
be unique.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

diablo (di****@noplace.com) writes:

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

Then use the syntax David posted first:

UPDATE product
SET quantity = quantity -
(SELECT SUM(quantity)
FROM cart
WHERE cart.buyerid = x
AND cart.productid = product.productid)
WHERE EXISTS
(SELECT *
FROM cart
WHERE cart.buyerid = x
AND cart.productid = product.productid) ;
The syntax Jens posted works on Microsoft SQL Server, which is what we
discuss in this newsgroup. But judging from the error message you are
using some other product, unknown which. You may getter better help
if you ask in a forum for that product.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[diablo]

Thanks the originnal one worked fine.

I am using Access 2000

Kal

[David Portas]

diablo wrote:

Thanks the originnal one worked fine.

I am using Access 2000

Kal

Access is not SQL Server. Ask in the right group in future.

--
David Portas
SQL Server MVP
--