asked on

Microsoft, SQL, 2005, Update Script Help Needed

Experts,

I have a field in my PatientProfile table - 'ReleaseOfInformationIndicatorDate' I wish to update. The only twist is I want to set this date equal to the patients last visit. That table is Patientvisit. How can I tell my update script to give me the date of last visit in this field right? If no visit, leave Null.

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

Open in new window

tigin44

try this

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

Jeff S

ASKER

I am so sorry, If no visit then set equal to current date.

So, I need it to first look for a date ... if none, set to todays date. Make sense?

tigin44

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

Jeff S

ASKER

I get this back:

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

tigin44

sory for my carefullnes... this does what you want

update pp
Set ReleaseOfInformationIndicatorDate = t.VisitDate
from PatientProfile pp
inner join (SELECT PatientProfileId, ISNULL(max(pv.visit), GETDATE()) AS VisitDate
FROM patientvisit pv
WHERE pp.PatientProfileId = pv.PatientProfileId) t ON t..PatientProfileId = pv.PatientProfileId

Jeff S

ASKER

Server: Msg 107, Level 16, State 2, Line 3
The column prefix 'pp' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 3
The column prefix 'pv' does not match with a table name or alias name used in the query.

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial