MsSQL - While, Loop
Posted on 2014-02-18
Hi all Experts
I am writing a statement as belows to update the cost from downside to topside by level.
Actually, I dont want to do "Update" statement for 8 times (from Level 8 to Level 1).
Is there any way to modify the statement instead of 8 times of update action?
Declare @intFlag INT
set @intFlag = 8
while @intFlag <= 8
set q.PMaterialCost = a.MC * q.NewUsage
from dbo.tbl_MBMBOM q
(select Product,Facility,Parent, sum(MaterialCost) as MC
from dbo.tbl_MBMBOM where period = 201401 and facility = 'AA' and [Level] = @intFlag + 1
group by Parent,Facility,Product
) as a
on q.child = a.Parent and q.Flag = a.Facility and q.Product = a.Product
where q.Period = 201401 and q.Facility = 'AA' and [Level] = @intFlag
SET @intFlag = @intFlag - 1
IF @intFlag = 1