declare @t table (a int, b int, d date, bsum int)
insert into @t(a,b, d) values 
 (1, 2, cast(getdate() as date))
,(1, 3, cast(getdate() as date))
,(3,4,cast(getdate() as date))


update t
set bsum = t1.bsum

from @t t
join (
	select a, sum(b) 
	from @t
	group by a) t1(a, bsum)
  on t1.a = t.a

select * from @t

quote:

---

Originally posted by gbritton

I mocked up your scenario with some temporary tables. Hopefully you can apply it to your actual situation:

declare @t table (a int, b int, d date, bsum int)
insert into @t(a,b, d) values 
 (1, 2, cast(getdate() as date))
,(1, 3, cast(getdate() as date))
,(3,4,cast(getdate() as date))


update t
set bsum = t1.bsum

from @t t
join (
	select a, sum(b) 
	from @t
	group by a) t1(a, bsum)
  on t1.a = t.a

select * from @t

Note that your multiple nested subqueries can be a performance killer. Look for the simplest approach first.

---