question about update/join query

[Andy Wallace]

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