List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:May 7 2008 4:35pm
Subject:question about update/join query
View as plain text  
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
Thread
Query/Key OptimizationMichael Stearne6 May
  • Re: Query/Key OptimizationMichael Stearne6 May
  • Re: Query/Key OptimizationKrishna Chandra Prajapati7 May
  • question about update/join queryAndy Wallace7 May
    • Re: question about update/join queryAndy Wallace7 May
    • Order ProblemNeil Tompkins7 May
Re: question about update/join queryAndy Wallace8 May
Re: question about update/join queryAndy Wallace8 May
Re: question about update/join queryAndy Wallace8 May
RE: Order ProblemNeil Tompkins8 May
  • Re: Order ProblemMoon's Father20 May
Re: question about update/join queryAndy Wallace8 May