Aggregate may not appear in an UPDATE statement

I am trying to update a field in a table with the most recent event_date. However, I am receiving the following error:

Msg 157, Level 15, State 1, Line 1

An aggregate may not appear in the set list of an UPDATE statement.

My code appears below:

-

update mraOI

set mraOI.event_history_key = eh.event_history_key,

mraOI.event_date = MAX(eh.event_date),

mraOI.event_time = eh.event_time,

mraOI.event_type_key = eh.event_type_key,

mraOI.item_key = eh.item_key,

mraOI.associated_item_ke = eh.associated_item_ke

FROM reports.MedRecActionOI mraOI INNER JOIN

srm.EVENT_HISTORY eh ON mraOI.CC_episode_key = eh.item_key

--

Can someone suggest a better method or a work around?

You could try this:

Code Snippet

update mraOI

set mraOI.event_history_key = eh.event_history_key,

mraOI.event_date = eh.event_date,

mraOI.event_time = eh.event_time,

mraOI.event_type_key = eh.event_type_key,

mraOI.item_key = eh.item_key,

mraOI.associated_item_ke = eh.associated_item_ke

FROM reports.MedRecActionOI mraOI INNER JOIN

srm.EVENT_HISTORY eh ON mraOI.CC_episode_key = eh.item_key

where eh.event_date = (select max(event_date) from srm.EVENT_HISTORY where item_key = mraOI.CC_episode_key)

I haven't tested it as I don't have your DB structure, but hopefully that'll help you.

One potential issue to be aware of. If there are more than one event with the same event_date you could get more than one row. If you have the time as well as the date it's very unlikely to happen, as times are accurate to 3ms. However if you're using smalldatetime they're only accurate to 1 minute, so the risk of getting more than one with the same value is greater.

Sean

|||This worked great, Sean! Thanks.