From: Andy Wallace
Date: May 7 2008 4:35pm
Subject: question about update/join query
List-Archive: http://lists.mysql.com/mysql/212684
Message-Id: <4821DA56.80104@cisdata.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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