CODEWITHSUNDEEP
sqlsql-server
Mattrajotte
Mattrajotte

Reputation: 45

SQL Update with SUM from other fields

I have tried many of the solutions available on Stack to solve this problem, but what seems to work for others, doesn't work for me. My situation is very simple and I seem to be unable to simplify the provided solutions down to what I need.

I have two tables:

master

M_ID, Name, total_number

things

T_ID, M_ID, thing, number

I can query the SUM, however I have been unable to update master.total_number with the sum. I've tried all sorts of solutions and I really think I am over thinking this so I give up!

Here is the query i wish to achieve, knowing full well this doesn't work as is:

UPDATE master
set master.total_number = (SELECT SUM(things.number) 
                           from things 
                           where M_ID = 1234) 
where master.M_ID = 1234

Upvotes: 0

Views: 3141

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Why not just do APPLY with update :

UPDATE m
     SET m.total_number = s.sum_no
FROM master m CROSS APPLY
     (SELECT SUM(t.number) as sum_no
      FROM things t
      WHER t.M_ID = m.M_ID
     ) s;
WHERE m.M_ID = 1234;

However, your update query needs to be correlated so, it would be :

UPDATE m
     SET M.total_number = (SELECT SUM(t.number) 
                           FROM things t
                           WHERE t.M_ID = m.M_ID
                          )
FROM master m 
WHERE m.M_ID = 1234;

Upvotes: 2

MJS
MJS

Reputation: 71

Try this...

UPDATE m
SET    m.total_number = theSum.Val
FROM   master  m
OUTER APPLY
(
    SELECT [Val] = SUM(number)
    FROM   things
    WHERE  things.M_ID = m.M_ID
) theSum
--WHERE  M_ID = 1234      -- <=== Optionally...just for a specific ID.

Upvotes: 0

Related Questions