question about update/join query

[Andy Wallace <awallace@xxxxxxxxxxx>]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx