Update table from sum of another table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Update table from sum of another table

Author

Topic

vinnyv911
Starting Member

2 Posts

Posted - 2012-08-08 : 11:38:23

i am trying to update a table as follows below, (except it updated all the fields with the same value). Can anyone see where i went wrong?

UPDATE Company
SET Comp_2009sales = OppSum
FROM (SELECT Comp_Companyid,SUM(oppo_totalorders) AS OppSum
FROM Company
left join opportunity on comp_companyid = oppo_primarycompanyid
Where oppo_solddate > '2011-12-01'
and oppo_totalorders != comp_2009sales
and oppo_deleted is null
and oppo_status = 'Won'
GROUP BY Comp_Companyid ) C
left join opportunity D on comp_companyid = oppo_primarycompanyid
WHERE D.oppo_primarycompanyid = C.Comp_Companyid

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2012-08-08 : 12:05:55

Ur Question seems to be wrong!
>> updated all the fields with the same value
should be
"All Rows in the field with the same value"

Reason :
U need to join the table which u r going to update as well.


UPDATE Company
SET Comp_2009sales = OppSum
From Company a
Inner Join 
(
SELECT Comp_Companyid,SUM(oppo_totalorders) AS OppSum
FROM Company
left join opportunity on ......
......
) c on a.Comp_Companyid = c.Comp_Companyid
Left Join join opportunity D on D.oppo_primarycompanyid = C.Comp_Companyid

I changed ur code a bit
I didn't copy the whole Group Sum query

Srinika

vinnyv911
Starting Member

2 Posts

Posted - 2012-08-08 : 13:08:06

Worked Great !
THANK YOU !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources