如何使用XML更新多行 [英] How to update multiple row with XML
问题描述
有两个表,一个是Product_mst,另一个是Stock_Master我有一个网页,用于放置多个产品的订单,使用XML插入多个产品,它工作正常,但同时我想更新我的Stock_master以减少库存数量为我添加更新块在订单sp我想更新股票产品明智bt现在它正在更新到所有行请帮助我,请在下面找到我的SP
我的尝试:
更改程序[dbo]。[InsertOrderDtls]
(
@OrderXml XML
)
AS
BEGIN
INSERT INTO order_dtls(OrderId,ProdId ,价格,数量,金额)
SELECT p.value('@ OrderId','INT'),p.value('@ ProdId','INT'),
p.value('@ Rate','FLOAT'),p。值('@ Qty','FLOAT'),p。值('@ Amount','FLOAT')
- ,p.value('@ AddedBy','INT')
- ,p.value('@ AddedOn ','SMALLDATETIME')
FROM @ OrderXml.nodes('/ ROOT / Order')n(p);
--RETURN @@ ROWCOUNT;
更新product_Mst
设置数量=数量 - q.value('@ Qty','FLOAT')
来自@ OrderXml.nodes('/ ROOT / Order')n(q的
);
- 更新Stock_Master
- 设置数量=数量 - 价值('@数量','FLOAT')
- 来自@ OrderXml.nodes('/ ROOT / Order')n(r)
- 其中ProductId = r.value('@ ProdId',' INT');
END
There are two tables one is Product_mst and other is Stock_Master I have one web page for placing order of multiple product im using XML to insert multiple product it is working fine but at the same time i want to update my Stock_master to reduce the stock qty for that i added update block on Order sp i want to update the stock product wise bt now it is updating to all rows Please help me please find my SP below
What I have tried:
ALTER PROCEDURE [dbo].[InsertOrderDtls]
(
@OrderXml XML
)
AS
BEGIN
INSERT INTO order_dtls (OrderId, ProdId, Rate,Qty, Amount)
SELECT p.value('@OrderId','INT'), p.value('@ProdId','INT'),
p.value('@Rate','FLOAT') ,p.value('@Qty','FLOAT'), p.value('@Amount','FLOAT')
--,p.value('@AddedBy','INT')
--,p.value('@AddedOn','SMALLDATETIME')
FROM @OrderXml.nodes('/ROOT/Order')n(p);
--RETURN @@ROWCOUNT;
update product_Mst
set Qty = Qty - q.value('@Qty','FLOAT')
from @OrderXml.nodes('/ROOT/Order')n(q);
--update Stock_Master
--set Qty = Qty - r.value('@Qty','FLOAT')
--from @OrderXml.nodes('/ROOT/Order')n(r)
--where ProductId = r.value('@ProdId','INT');
END
推荐答案
尝试这样的事情:
Try something like this:
UPDATE
M
SET
Qty = Qty - q.value('@Qty','FLOAT')
FROM
product_Mst As M
INNER JOIN @OrderXml.nodes('/ROOT/Order') As n(q)
ON q.value('@ProdId','INT') = M.ProductId
;
UPDATE
M
SET
Qty = Qty - q.value('@Qty','FLOAT')
FROM
Stock_Master As M
INNER JOIN @OrderXml.nodes('/ROOT/Order') As n(q)
ON q.value('@ProdId','INT') = M.ProductId
;
这篇关于如何使用XML更新多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!