Update column based on aggregation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update column based on aggregation

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2009-01-19 : 07:41:47

t1
id int (PK)
mySum float

t2
id
t1_id (FK)
value float

I want to summarize t2.value for the specific id from t1 and update t1.mySum

I tried to solve it by using a function but did not get it to work as expected.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-19 : 07:46:07

update t2
set value = t.mysum
from t2
inner join
t1 t on t.id = t2.t1_id

maevr
Posting Yak Master

169 Posts

Posted - 2009-01-19 : 07:54:06

Thanks for fast reply but I wont work since t2 can contain multiple rows of the same t1_id.

select t1_id
,sum(value)
from t2
group by t1_id

This should return the values that I want to use for the update, my problem is how I write the update clause.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-19 : 07:56:06

[code]update
target
set
mySum=source.mySum
from t1 as target inner join
(
select id, sum(value) as mySum from t2 group by id
) as source
on target.id=source.id
[/code]

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-19 : 07:59:39

update t2
set value = t.sumvalue
from t2
inner join
(select t1_id
,sum(value) as sumvalue
from t2
group by t1_id)t
on t.id = t2.t1_id

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-19 : 08:02:34

quote:
Originally posted by bklr

update t2
set value = t.sumvalue
from t2
inner join
(select t1_id
,sum(value) as sumvalue
from t2
group by t1_id)t
on t.id = t2.t1_id

You should update t1. See my previous reply

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 08:20:30

And Madhi should group over foreign key

INNER JOIN only updates existing records (matches).
What will you do for those t1.id values not present in t2 table?

With inner join, they keep their values.
Try this

UPDATE		tgt
SET		tgt.mySum = COALESCE(x.mySum, 0)	-- If COALESCE is not present, a NULL will be inserted
FROM		t1 AS tgt
LEFT JOIN	(
			SELECT		t1_id,
					SUM(value) AS mySum
			FROM		t2
			GROUP BY	t1_id
		) AS x ON x.t1_id = tgt.id

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources