Update multiple values in a single statement

Question

I have a master / detail table and want to update some summary values in the master table against the detail table. I know I can update them like this:

update MasterTbl set TotalX = (select sum(X) from DetailTbl where DetailTbl.MasterID = MasterTbl.ID)
update MasterTbl set TotalY = (select sum(Y) from DetailTbl where DetailTbl.MasterID = MasterTbl.ID)
update MasterTbl set TotalZ = (select sum(Z) from DetailTbl where DetailTbl.MasterID = MasterTbl.ID)

But, I'd like to do it in a single statement, something like this:

update MasterTbl set TotalX = sum(DetailTbl.X), TotalY = sum(DetailTbl.Y), TotalZ = sum(DetailTbl.Z)
from DetailTbl
where DetailTbl.MasterID = MasterTbl.ID group by MasterID

but that doesn't work. I've also tried versions that omit the "group by" clause. I'm not sure whether I'm bumping up against the limits of my particular database (Advantage), or the limits of my SQL. Probably the latter. Can anyone help?

Answer 1

Try this:

 Update MasterTbl Set
    TotalX = Sum(D.X),    
    TotalY = Sum(D.Y),    
    TotalZ = Sum(D.Z)
 From MasterTbl M Join DetailTbl D
    On D.MasterID = M.MasterID

Depending on which database you are using, if that doesn't work, then try this (this is non-standard SQL but legal in SQL Server):

 Update M Set
    TotalX = Sum(D.X),    
    TotalY = Sum(D.Y),    
    TotalZ = Sum(D.Z)
 From MasterTbl M Join DetailTbl D
     On D.MasterID = M.MasterID