Update Parent Table column from child table column values

Question

I have a table SaleOrder(SO) and SaleOrderDetail(SOD) with one to may relation ship. ID and SOID are primary key foreign key. i need to update SO Table with Values of SOD Tables after some aggregation based on primary key. please see below.

SO
-----------------------------------
ID  SaleOrderQty
1   --
2   --

SOD
-------------------------------------
SOID    Qty PerPack
1       3   10
1       7   6
2       4   5
2       5   8

multiply Qty with PerPack

1   3*10 = 30
1   7*6 = 42
2   4*5 = 20
2   5*8 = 40

and add up all multiplication results based on keys

1   30+42 = 72
2   20+40 = 60

and update Parent table

SO
-----------------------------------
ID  SaleOrderQty
1   72
2   60

i tried this

Declare @Id varchar(50)
declare @Next int
set @Next =1
WHILE @Next <= 30
Begin
    Select @Id = Id From SO Where SOSerial=@Next

    Update SO 
    Set SaleOrderQty = (SELECT  sum((SOD.Quantity* SOD.PerPack)) total
                        FROM  SO INNER JOIN
                        SOD ON SO.Id = SOD.SOId
                        WHERE SOD.SOId=@Id
                        group by SOD.SOId)


                        set @Next=@Next+1
                        --print @Id
End

sums are ok but it set all SaleOrderQty values with the last sum.

One more thing. i have 30 records in parent table. when query completes it shows 30 messages.

Answer 1

Per my prior comment, you want to avoid loops.

update so
set SaleOrderQty = a.calc
from SO so
join (select sod.soid,sum((SOD.Quantity* SOD.PerPack)) as calc
        from sod
        group by sod.SOID) a on a.SOID=so.ID

where so.SaleOrderQty is null --optional