An Aggregate may not appear in the set list

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)

An Aggregate may not appear in the set list

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-11-03 : 15:47:57

I am trying to write an update statement based on an aggregate and it will not let me. Please find below the SQL.

update pp
Set ReleaseOfInformationIndicatorDate = IsNull(max(pv.visit),GETDATE())
from PatientProfile pp
inner join patientvisit pv on pp.PatientProfileId = pv.PatientProfileId

and this is the error message:

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

Can someone tell me how to get round this please?

Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-03 : 16:04:36

[code]update pp
Set pp.ReleaseOfInformationIndicatorDate = COALESCE(pv.visit, GETDATE())
from PatientProfile as pp
LEFT join (
select PatientProfileId,
max(visit) AS visit
from patientvisit
) as pv on pv.PatientProfileId = pp.PatientProfileId[/code]

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