SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-10-2008, 02:03 PM
| ||||
| ||||
 Re: question about update/join query 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@cisdata.net> 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@cisdata.net> >>> To: "Martin" <mgainty@hotmail.com> >>> Cc: "mysql list" <mysql@lists.mysql.com> >>> 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@cisdata.net> >>>>> To: "mysql list" <mysql@lists.mysql.com> >>>>> 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@cisdata.net >>>>>> >>>>>> -- >>>>>> MySQL General Mailing List >>>>>> For list archives: http://lists.mysql.com/mysql >>>>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mgainty@hotmail.com >>>>>> >>>>>> >>>> -- >>>> Andy Wallace - CISData - IDX Slave >>>> AIM: acmwallace awallace@cisdata.net >>>> >> -- >> Andy Wallace - CISData - IDX Slave >> AIM: acmwallace awallace@cisdata.net >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@gmail.com >> >> -- Andy Wallace - CISData - IDX Slave AIM: acmwallace awallace@cisdata.net      |
|
05-20-2008, 05:55 PM
| ||||
| ||||
| Re: Order Problem That is fine. On Thu, May 8, 2008 at 4:51 PM, Neil Tompkins <neildtompkins@hotmail.com> wrote: > Perfect. It worked just how I wanted. > > Thanks for your help. > > Neil > > > > > Date: Wed, 7 May 2008 19:54:39 +0200> To: neildtompkins@hotmail.com> > Subject: Re: Order Problem> From: leannonn@gmail.com> > Hi,> > You should > look at the `FIND_IN_SET` function here: > > http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set> > > Your query could look like:> SELECT ProductID FROM Products WHERE Enabled= > ' Yes' AND ProductID > IN(varProductID) ORDER BY FIND_IN_SET(ProductID, > varProductID);> > Haven't tested it, though...> > > Take care,> Aleksandar > __________________________________________________ _______________ > > Discover and Win with Live Search > > http://clk.atdmt.com/UKM/go/msnnkmgl...ukm/direct/01/ > -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn |
Linear Mode
