List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:May 8 2008 3:31pm
Subject:Re: question about update/join query
View as plain text  
Brent -
thanks, now I understand. My aversion to subqueries is for performance
against a very large table, which event_log promises to be. I hope to
minimize this with some time boundaries on that table (where event_time
between x and y).

But thanks, I'll play with that. And thanks Martin for the start.

andy

Brent Baisley wrote:
> You can do it in a single UPDATE statement, but you do need a form of
> a subquery. Why the aversion of a subquery? The simplest approach is
> to first get what you want using a SELECT statement. Then change
> SELECT to UPDATE and add your SET statement.
> In your case there is a little twist because you have to use a group
> by to get the max. So first get the users and latest event time, as
> Martin stated.
> 
> SELECT user_id, MAX(event_time) maxtime FROM event_log GROUP by user_id
> 
> That can be used to create a "virtual" table you can join against for
> your update statement.
> 
> UPDATE user JOIN
> (SELECT user_id, MAX(event_time) maxtime FROM event_log GROUP by
> user_id) AS eMax
> ON user.user_id=eMax.user_id
> SET last_visit=maxtime WHERE user.user_id=eMax.user_id
> 
> That should do it, although I don't think you need the WHERE clause.
> That will do a full table scan on the event_log table, which can be
> very bad if it is large. You can work around this by compiling groups
> of users at a time. This will require querying the users and for the
> group of users and joining on the event_log table.
> 
> Brent Baisley
> Systems Architect
> 
> 
> On Thu, May 8, 2008 at 12:26 AM, Andy Wallace <awallace@stripped> wrote:
>> 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=1
>>>>>>
>>>>>>
>>>> --
>>>> Andy Wallace - CISData - IDX Slave
>>>> AIM: acmwallace   awallace@stripped
>>>>
>> --
>> 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