List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:May 7 2008 10:21pm
Subject:Re: question about update/join query
View as plain text  
I want to put only the max date into the field... I was thinking that max
was a group function, but now that I type that out loud, perhaps I'm not
using all the neurons available... hmmm...


thanks,
andy

Martin wrote:
> Hi Andy-
> 
> Is there a reason why you are using Query group by clause in UPDATE 
> statement?
> 
> M
> ----- Original Message ----- From: "Andy Wallace" <awallace@stripped>
> To: "mysql list" <mysql@stripped>
> Sent: Wednesday, May 07, 2008 1:07 PM
> Subject: Re: question about update/join query
> 
> 
>> 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
>>
>> -- 
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
> 

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