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

User · Answer

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

User · Answer

In Oracle the solution would be   UPDATE     MasterTbl SET      TotalX TotalY TotalZ           SELECT SUM X  SUM Y  SUM Z           from DetailTbl where DetailTbl MasterID   MasterTbl ID    Don t know if your system allows the same

User · Answer

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

User · Answer

Have you tried with a sub-query for every field   UPDATE     MasterTbl SET     TotalX    SELECT SUM X  from DetailTbl where DetailTbl MasterID   MasterTbl ID       TotalY    SELECT SUM Y  from DetailTbl where DetailTbl MasterID   MasterTbl ID       TotalZ    SELECT SUM Z  from DetailTbl where DetailTbl MasterID   MasterTbl ID  WHERE

User · Answer

Try this   update MasterTbl M          select sum X  as sX                 sum Y  as sY                 sum Z  as sZ                 MasterID         from   DetailTbl         group by MasterID  A set   M TotalX A sX    M TotalY A sY    M TotalZ A sZ where   M ID A MasterID

User · Answer

If your DB supports it  concatenating all 3 updates into one sql string will save on server-round-trips if querying over the LAN  So if nothing else works  this might give you a slight improvement  The typical  multi-statement delimiter is the semi-colon  eg    update x     update y    update   z

User · Answer

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

User · Answer

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

User · Answer

In Oracle the solution would be   UPDATE     MasterTbl SET      TotalX TotalY TotalZ           SELECT SUM X  SUM Y  SUM Z           from DetailTbl where DetailTbl MasterID   MasterTbl ID    Don t know if your system allows the same

User · Answer

In Oracle the solution would be   UPDATE     MasterTbl SET      TotalX TotalY TotalZ           SELECT SUM X  SUM Y  SUM Z           from DetailTbl where DetailTbl MasterID   MasterTbl ID    Don t know if your system allows the same

User · Answer

Try this   update MasterTbl M          select sum X  as sX                 sum Y  as sY                 sum Z  as sZ                 MasterID         from   DetailTbl         group by MasterID  A set   M TotalX A sX    M TotalY A sY    M TotalZ A sZ where   M ID A MasterID

User · Answer

Have you tried with a sub-query for every field   UPDATE     MasterTbl SET     TotalX    SELECT SUM X  from DetailTbl where DetailTbl MasterID   MasterTbl ID       TotalY    SELECT SUM Y  from DetailTbl where DetailTbl MasterID   MasterTbl ID       TotalZ    SELECT SUM Z  from DetailTbl where DetailTbl MasterID   MasterTbl ID  WHERE

User · Answer

Try this   update MasterTbl M          select sum X  as sX                 sum Y  as sY                 sum Z  as sZ                 MasterID         from   DetailTbl         group by MasterID  A set   M TotalX A sX    M TotalY A sY    M TotalZ A sZ where   M ID A MasterID

User · Answer

In Oracle the solution would be   UPDATE     MasterTbl SET      TotalX TotalY TotalZ           SELECT SUM X  SUM Y  SUM Z           from DetailTbl where DetailTbl MasterID   MasterTbl ID    Don t know if your system allows the same

User · Answer

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

User · Answer

If your DB supports it  concatenating all 3 updates into one sql string will save on server-round-trips if querying over the LAN  So if nothing else works  this might give you a slight improvement  The typical  multi-statement delimiter is the semi-colon  eg    update x     update y    update   z

User · Answer

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

User · Answer

Have you tried with a sub-query for every field   UPDATE     MasterTbl SET     TotalX    SELECT SUM X  from DetailTbl where DetailTbl MasterID   MasterTbl ID       TotalY    SELECT SUM Y  from DetailTbl where DetailTbl MasterID   MasterTbl ID       TotalZ    SELECT SUM Z  from DetailTbl where DetailTbl MasterID   MasterTbl ID  WHERE

User · Answer

Try this   update MasterTbl M          select sum X  as sX                 sum Y  as sY                 sum Z  as sZ                 MasterID         from   DetailTbl         group by MasterID  A set   M TotalX A sX    M TotalY A sY    M TotalZ A sZ where   M ID A MasterID

User · Answer

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

User · Answer

Have you tried with a sub-query for every field   UPDATE     MasterTbl SET     TotalX    SELECT SUM X  from DetailTbl where DetailTbl MasterID   MasterTbl ID       TotalY    SELECT SUM Y  from DetailTbl where DetailTbl MasterID   MasterTbl ID       TotalZ    SELECT SUM Z  from DetailTbl where DetailTbl MasterID   MasterTbl ID  WHERE

User · Answer

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

[sql] Update multiple values in a single statement

Examples related to sql

Examples related to advantage-database-server