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?

Charles Bretana · Answer 1 · 2022-05-16T15:14:08.003

38

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

edited May 16 '22 at 15:14

answered Nov 14 '08 at 02:55

Charles Bretana

143,358
22
150
216

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? – Kluge Nov 14 '08 at 14:46
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 – Charles Bretana Nov 14 '08 at 15:34
2

This is not legal SQL. There is no 'FROM' clause in an update. http://dev.mysql.com/doc/refman/6.0/en/update.html – Martin York Nov 18 '08 at 15:42
+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! – Mark Brittingham Feb 03 '09 at 23:14

score 9 · Answer 2 · answered Nov 14 '08 at 00:46

9

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

answered Nov 14 '08 at 00:46

Chris

27,596
25
124
225

@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. – Kluge Nov 14 '08 at 00:49
I have now clarified my original question to show that I've tried omitting the "group by" clause. Thanks! – Kluge Nov 14 '08 at 00:51
What database server / version are you using? When you say my SQL, do you mean "my SQL skills" or "mySQL, the server"? – Chris Nov 14 '08 at 00:54
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 :( – Chris Nov 14 '08 at 00:55

score 4 · Answer 3 · answered Nov 14 '08 at 13:06

4

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.

answered Nov 14 '08 at 13:06

Dave Costa

47,262
8
56
72

My database doesn't allow that, but it's nice to see the approach other database vendors have taken. – Kluge Nov 14 '08 at 14:44

score 3 · Answer 4 · edited Nov 14 '08 at 03:15

3

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

edited Nov 14 '08 at 03:15

tvanfosson

524,688
99
697
795

answered Nov 14 '08 at 01:56

Milen A. Radev

60,241
22
105
110

@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. – Kluge Nov 14 '08 at 02:21

Martin York · Answer 5 · 2008-11-18T15:46:48.757

2

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

edited Nov 18 '08 at 15:46

answered Nov 14 '08 at 03:13

Martin York

257,169
86
333
562

score 1 · Answer 6 · answered May 04 '10 at 21:13

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'

Update multiple values in a single statement

6 Answers6