From: Andy Wallace
Date: May 8 2008 4:26am
Subject: Re: question about update/join query
List-Archive: http://lists.mysql.com/mysql/212691
Message-Id: <4822810F.1040200@cisdata.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ok, I think I need to try to restate my problem.

I have an event_log table, which tracks events (!). Basic structure
is:

table: event_log
    event_time  timestamp
    event_id    int
    user_id     int

and my user table:

table: user
    user_id     int
    name        varchar(50)
    last_visit  datetime


I want to run a query that updates the last_visit column of user with
the MAX(event_time) row for which the user_id's match, but only if I
find an event:

update user U
set U.last_visit = (select max(L.event_time) from event_log L
                     where L.user_id = U.user_id)

I would like to do it without a subquery, I thought that the
multiple table syntax for UPDATE would do it, but I can't wrap
my head around it.

UPDATE user U, event_log L
SET U.last_visit = MAX(L.event_time)
WHERE U.user_id = L.user_id
GROUP BY L.event_time

I guess the main question is - CAN I do this? Or will I have to resort
to either a subquery, or external processing?

thanks,
andy











Martin wrote:
> Hi Andy-
> 
> the MAX function needs group by for the column for which it calculating 
> max value as in this example
> (select MAX(EL.event_time)
> //                     from event_log EL
> //                     where EL.enduser_acnt = E.enduser_acnt
> 
> //Inner join forces selection on columns which contain non null values 
> as seen here
>                      from event_log AS EL INNER JOIN Event AS E
>                      ON EL.enduser_acnt = Event.enduser_anct
> 
>                       group by EL.event_time);
> //                     group by EL.enduser_acnt);
> 
> HTH
> Martin
> ----- Original Message ----- From: "Andy Wallace" <awallace@stripped>
> To: "Martin" <mgainty@stripped>
> Cc: "mysql list" <mysql@stripped>
> Sent: Wednesday, May 07, 2008 6:21 PM
> Subject: Re: question about update/join query
> 
> 
>> 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=mgainty@stripped
>>>>
>>>>
>>>
>>
>> -- 
>> Andy Wallace - CISData - IDX Slave
>> AIM: acmwallace   awallace@stripped
>>
> 

-- 
Andy Wallace - CISData - IDX Slave
AIM: acmwallace   awallace@stripped