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@stripped
