Clarification: I DON'T want to update the last_visit field if there
is no matching event record...

I managed to get this to sort of work:

update enduser E
set E.last_visit = (select MAX(EL.event_time)
from event_log EL
where EL.enduser_acnt = E.enduser_acnt
group by EL.enduser_acnt);

but it updated the last_visit field to the default value if it found
no matching event_log row... which I don't want to happen.

thanks,
andy

Andy Wallace wrote:
> Hey all -
> I have two tables - an event_log table, and a user table. There is
> a "last_visit" column in the user table, and I want to update it from
> the event_log with the most recent event timestamp. And I want to do
> it without a subquery, eventually, both these tables will be pretty
> large, especially the event_log.
>
> I tried this:
>
> update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt
> set E.last_visit = MAX(EL.event_time)
> group by EL.enduser_acnt
>
> but I get an error on the group by. The pertinent tables sections are:
>
> table event_log
> event_time TIMESTAMP
> enduser_acnt int
>
> table enduser
> enduser_acnt int
> last_visit datetime
>
> Any help appreciated. Thanks...
> andy
>
>

--
Andy Wallace - CISData - IDX Slave
AIM: acmwallace awallace@cisdata.net