Menu
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?
400
One way to update multiple rows is to write multiple UPDATE statements. They can be separated with a semicolon (;) and submitted as a group (called a batch). Alternatively, use an UPDATE with a WHERE clause.
We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required.
MySQL a-z in TeluguColumn values on multiple rows can be updated in a single UPDATE statement if the condition specified in WHERE clause matches multiple rows. In this case, the SET clause will be applied to all the matched rows.
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 Why are you doing a group by on an update statement? Are you sure that's not the part that's causing the query to fail? Try this:
update MasterTbl set TotalX = Sum(DetailTbl.X), TotalY = Sum(DetailTbl.Y), TotalZ = Sum(DetailTbl.Z) from DetailTbl where DetailTbl.MasterID = MasterID If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
