SQL查询更新 [英] sql query update
问题描述
嗨
i需要帮助制定一个查询，该查询将更新一个
表中的字段，具体取决于另一个表中的值，例如
i有购物车桌子：cartid，buyerid，productid，数量
i有产品表：productid，数量
i想做一个：
从购物车中选择*，其中buyerid = x
然后根据我想要的返回行数更新产品表：
更新产品数量=数量 - cart.quantity
productid = cart.productid
如果你明白我的意思？
任何帮助表示赞赏
Kal
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
推荐答案
更新产品
SET数量=数量 -
（SELECT SUM（数量）
FROM cart
WHERE cart.buyerid = x
AND cart.productid = product.productid）
WHERE EXISTS
（SELECT *
FROM购物车
WHERE cart.buyerid = x
AND cart.productid = product.productid）;
- -
David Portas
SQL Server MVP
-
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
--
嗨Kal，
你肯定应该处理你所做的事情
减去库存的情况，但查询应该是：
更新产品
SET数量=数量 - c.quantity
来自产品p
INNER JOIN购物车c
ON p.productid = c.productid
HTH，Jens Suessmeyer。
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.
>
更新产品
SET数量=数量 - c.quantity
来自产品p
INNER JOIN购物车c
ON p.productid = c.productid
UPDATE product
SET quantity = quantity - c.quantity
FROM product p
INNER JOIN Cart c
ON p.productid=c.productid
嗨
这看起来像我想要的但却出错了
缺少运算符来自...
感谢
Kal
Hi
This looked like what i wanted but it gives an error
"missing operator" FROM...
Thank
Kal
这篇关于SQL查询更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
