List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:May 7 2008 5:07pm
Subject:Re: question about update/join query
View as plain text  
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
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