统计细表中的数据并将其赋给主表update dbo.P_Product
set StockQuantity=(select SUM(PD.Quantity) from dbo.P_Product P
left join dbo.P_ProductDetail PD
on PD.ProductID=P.ID)
这样写有错吗,怎么感觉不对啊,好像没有一一对应
set StockQuantity=(select SUM(PD.Quantity) from dbo.P_Product P
left join dbo.P_ProductDetail PD
on PD.ProductID=P.ID)
这样写有错吗,怎么感觉不对啊,好像没有一一对应
set P.StockQuantity=PD.Quantity
from dbo.P_Product P
left join
(
select SUM(Quantity) Quantity,ProductID from dbo.P_ProductDetail group by ProductID
)
PD
on PD.ProductID=P.ID
-- 这个看着更直观
update dbo.P_Product
set StockQuantity=PD.Quantity
from P_Product P,
(
select ProductID,SUM(PD.Quantity)
from dbo.P_ProductDetail
group by ProductID
)PD
where PD.ProductID=P.ID
set StockQuantity=SUM(PD.Quantity)
from dbo.P_Product P left join dbo.P_ProductDetail PD
on PD.ProductID=P.ID
update P set P.StockQuantity=PD.Quantity
from dbo.P_Product P left join
(select SUM(Quantity) Quantity,ProductID from dbo.P_ProductDetail group by ProductID) PD
on PD.ProductID=P.ID
set StockQuantity=
(select SUM(n.Quantity) from dbo.P_ProductDetail n where where n.ProductID=m.ID)
from dbo.P_Product m
不能将值 NULL 插入列 'StockQuantity',表 'CaiShangWebSiteDevelop.dbo.P_Product';列不允许有 Null 值。UPDATE 失败。
语句已终止。
set StockQuantity=(select isnull(SUM(PD.Quantity),0) from dbo.P_Product P
left join dbo.P_ProductDetail PD
on PD.ProductID=P.ID)
你的这个语句。。会把所有的数据赋成所有明细的和
加个isnull就OK
update P
set P.StockQuantity=PD.Quantity from dbo.P_Product P left join
(select SUM(Quantity) Quantity,ProductID from dbo.P_ProductDetail group by ProductID)
PD on PD.ProductID=P.ID用连接求和分组是必须的