Update Query - SQL Server Forums

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 Query

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-03-25 : 07:42:45

Dear All,

I am using below query to update

UPDATE R
SET R.TRIMONTHLY_CHARGES=SUM(ISNULL(A.TOTAL_CHARGES,0))
FROM ROLLING_3_MONTH_CHARGES R
INNER JOIN INVOICE_HEADER A on R.ORIGINAL_FSC = A.ORIGINAL_FSC
INNER JOIN PROVIDER_DIMENSION B
ON A.GROUP_NUMBER = B.GROUP_NUMBER
AND A.ID_102 = B.ID102
AND A.ID_202 = B.ID202
AND A.ID_3 = B.ID3

But its throwing an error:
Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.

How can we do this update please help.

Thanks,
GAnagdahr

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-25 : 07:48:17

Maybe this???


UPDATE roll SET
	TRIMONTHLY_CHARGES = calc.Summed
FROM
	ROLLING_3_MONTH_CHARGES roll

	JOIN (
		SELECT
			R.ORIGINAL_FSC AS ORIGINAL_FSC
			, SUM(ISNULL(A.TOTAL_CHARGES, 0)) AS Summed
		FROM
			ROLLING_3_MONTH_CHARGES R

			INNER JOIN INVOICE_HEADER A on R.ORIGINAL_FSC = A.ORIGINAL_FSC 

			INNER JOIN PROVIDER_DIMENSION B ON
				A.GROUP_NUMBER = B.GROUP_NUMBER 
				AND A.ID_102 = B.ID102 
				AND A.ID_202 = B.ID202 
				AND A.ID_3 = B.ID3
			GROUP BY
				R.ORIGINAL_FSC
		)
		calc ON calc.ORIGINAL_FSC = roll.ORIGINAL_FSC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-25 : 07:49:50

sum of TOTAL_CHARGES,you want to group by based on what column in the INVOICE_HEADER ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources