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?

Pulvinus

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

As mentioned in comments, if your database software does not allow the use of From clauses in Updates, then you must use the subquery approach mentioned in several other answers

Pedrick

I tried both of your approaches and couldn't get them to work. My database only supports SQL-92 with a few extensions. What database are you using that supports the code you suggest? – Pulvinus

SQL Server supports this syntax... If your database does not support this, then I think you're stuck with the multiple subquery approach.. as recommended by Milen below – Pedrick

This is not legal SQL. There is no 'FROM' clause in an update. dev.mysql.com/doc/refman/6.0/en/update.html – Comenius

+1 Charles - I stumbled on to this question - and your answer - as I started to ask my own question. I upvoted you here because you just bailed me out of a tough SQL situation. Thanks for showing the "From" syntax in an Update! – Vulcan

Answer 2

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

Bikaner

@Chris, that doesn't work for me either. If it works for you, then I'm probably bumping into a limitation of my particular database. – Pulvinus

I have now clarified my original question to show that I've tried omitting the "group by" clause. Thanks! – Pulvinus

What database server / version are you using? When you say my SQL, do you mean "my SQL skills" or "mySQL, the server"? – Bikaner

Nevermind, I see you said Advantage. I don't have a test box for that or I'd try to find you a working query. Sorry :( – Bikaner

Answer 3

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.

Semaphore

My database doesn't allow that, but it's nice to see the approach other database vendors have taken. – Pulvinus

Answer 4

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
    ....

Vinson

@Milen, You are right, that would work. However, I assume that it would be no faster than the three statements in my example. I guess I should go try both ways and compare the timings. – Pulvinus

Answer 5

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

Comenius

Answer 6

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'

Trstram