From: Andy Wallace Date: May 7 2008 10:21pm Subject: Re: question about update/join query List-Archive: http://lists.mysql.com/mysql/212688 Message-Id: <48222B82.9080508@cisdata.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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" > To: "mysql list" > 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=mgainty@stripped >> >> > -- Andy Wallace - CISData - IDX Slave AIM: acmwallace awallace@stripped