An Aggregate may not appear in the set list of an update statement

[JBourne77]

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

 

[RiverGuy]

Try this (untested)

update pp
Set ReleaseOfInformationIndicatorDate = IsNull(MaxVisit, GETDATE())
from PatientProfile pp
inner join 
	(SELECT PatientProfileId, MAX(visit) AS MaxVisit FROM patientvisit GROUP BY PatientProfileID) pv 
	on pp.PatientProfileId = pv.PatientProfileId

 

[JBourne77]

RiverGuy

When I do SELECT * FROM PatientProfile WHERE PatientProfileID = '44044' (my test patient), the ReleaseOfInformationIndicatorDate = NULL.

When I use your query along with a WHERE clause (Where pp.PatientProfileID = '44044'), I get a response back of 0 rows affected. I created this patient today for this test, therefore the ReleaseOfInformationIndicatorDate should have been the GETDATE().

Im not sure whats wrong.

 

[gmmastros]

try...

update pp
Set ReleaseOfInformationIndicatorDate = IsNull(MaxVisit, GETDATE())
from PatientProfile pp
[!]Left[/!] join 
    (SELECT PatientProfileId, MAX(visit) AS MaxVisit FROM patientvisit GROUP BY PatientProfileID) pv 
    on pp.PatientProfileId = pv.PatientProfileId

Changing from inner join to left join should resolve this issue. Since you are updating data, I encourage you to have a good backup first.

By the way, if this works, then it's because you don't have any matching rows in the PatientVisit table, or the row(s) you do have contain NULL for the visit column.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom