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?
This question is related to
sql
advantage-database-server
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
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
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
....
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
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'
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.
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
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
....
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
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
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
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.
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
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
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
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
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
....
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.
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'
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
....
Source: Stackoverflow.com