CODEWITHSUNDEEP
sqlsql-serveraggregate-functionssql-update
tptcat
tptcat

Reputation: 3961

UPDATE a field in one table with the SUM of another

I'm trying to set the value of a field in a table to the SUM of another set of fields in another table by:

UPDATE table1 
SET fieldToUpdate = 
(
    SELECT SUM(fieldToSum) FROM table2
) 
WHERE thirdField = 'A'

but I'm not having any luck. I've seen a lot of examples that use joins, but my 2 tables aren't related in any way.

Thanks

Upvotes: 0

Views: 4216

Answers (3)

tptcat
tptcat

Reputation: 3961

As stated in my comment above, I was attempting to update fieldToUpdate with a value that exceeded the width of that column. Thanks for everyone's assistance.

Upvotes: 0

Justin Pihony
Justin Pihony

Reputation: 67085

As your tables are not related, it looks like what you have already written should work. All rows in table1 with fieldToUpdate = 'A' will have the fieldToUpdate's column set to the sum of all fieldToSum's in table2. Maybe you can clarify your question for a better answer? As in, what exactly is the problem you are getting or a sample dataset and results expected

UPDATE:

Based on your new comments, if the previous value is NULL, then doing something like fieldToUpdate = [ANY VALUE] will result in no rows being returned. You would need to use fieldToUpdate IS NULL. Or, you could keep the = if you do something like this: ISNULL(fieldToUpdate, 'A') = 'A'.

Last, as a debugging measure, you can check @@ROWCOUNT after your update to see if it is even updating anything (which would affirm the need for IS NULL).

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280381

While the subquery should work, you could break it up like this:

DECLARE @s INT;

SELECT @s = SUM(fieldToSum) FROM dbo.table2;

UPDATE dbo.table1
SET fieldToUpdate = @s
WHERE fieldToUpdate = 'A';

Upvotes: 4

Related Questions